Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have two tables: TABLE1 and TABLE2.

And I am running this select query to fetch the data. It takes long time to run:

SELECT TABLE1.* , TABLE2.COL1 , TABLE2.COL4 FROM TABLE1, TABLE2     
WHERE TABLE1.COL1 IN ('1','TEXT','HO TED')     
  AND TABLE1.COL2= 1800
  AND TABLE2.COL1 = 10519198     
  AND TABLE1.COL3>= 100
  AND TABLE1.COL4= TABLE2.COL2
  AND TABLE2.COL3=('TEXT')

How could I improve the performance of this select query? Does too many AND affect my performance?

share|improve this question
    
What indexes do you have? What's the explain plan? –  Mat Apr 8 at 11:41
    
@Mat No i dont have any indexes –  san krish Apr 8 at 11:46
    
This is the solution. You should use indices. What type of them, and how, it depends on the distribution of your tables. –  Peter Horvath Apr 8 at 11:52
    
@PeterHorvath Any common rules to create the index ? or things i should keep in mind while creating a index –  san krish Apr 8 at 11:58
1  
@sankrish Yes, there are a lot, but it depends really on the exact size and data distribution of your tables. This is because you didn't get a good answer until now - simply you didn't said the needed informations to it. Maybe a "how to index complex queries tutorial" or such search on google... –  Peter Horvath Apr 8 at 12:10

1 Answer 1

up vote 3 down vote accepted

Based on your comments, here is a maybe faster version:

SELECT TABLE1.* , TABLE2.COL1 , TABLE2.COL4
FROM TABLE1
INNER JOIN TABLE2 ON TABLE1.COL4 = TABLE2.COL2
WHERE TABLE1.COL1 IN ('1','TEXT','HO TED')     
  AND TABLE1.COL2=1800
  AND TABLE1.COL3>=100
  AND TABLE2.COL1=10519198     
  AND TABLE2.COL3=('TEXT')

And you need to create a tree-index on TABLE1(COL2, COL1, COL3). Beware: hash-based index weren't o.k, because of your condition of TABLE1.COL3>=100 is an interval-query, which is impossible to be fastened by hash-based indices! This is also the cause, why must be COL3 the last element of the TABLE1(COL2, COL1, COL3) index.

Another index which can make things better were a hash-based index on TABLE2(COL1,COL3).

Further indices which will probably help: TABLE1(COL4) and TABLE2(COL2) (both).

None of the indices should be unique.

share|improve this answer
2  
I would first try an index on table1 where col2 is the leading column. As a rule of thumb, columns used with an equality operator should go first. The OR condition for col1 won't probably benefit very much from the index. –  a_horse_with_no_name Apr 8 at 12:36
    
@a_horse_with_no_name It is not an OR, it is a lookup in a (quite small, and fixed constant) set. The sql optimizer can probably handle this, but I optimize the query for the case if it couldn't. –  Peter Horvath Apr 8 at 12:40
    
@PeterHorvath col1 IN ('1','TEXT','HO TED') is equivalent to (col1='1' OR col1='TEXT' OR col1='HO TED') –  ypercube Apr 8 at 12:48
    
@ypercube Yes, it is equivalent, but can (and surely will) be implemented with a set intersection search and not with different, separate queries. –  Peter Horvath Apr 8 at 12:57
    
@PeterHorvath please explain about interval-query and also why you are preferring for tree-index for table 1 –  san krish Apr 8 at 13:19

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.