1

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?

8
  • What indexes do you have? What's the explain plan?
    – Mat
    Apr 8, 2014 at 11:41
  • @Mat No i dont have any indexes
    – Santhosh
    Apr 8, 2014 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.
    – peterh
    Apr 8, 2014 at 11:52
  • @PeterHorvath Any common rules to create the index ? or things i should keep in mind while creating a index
    – Santhosh
    Apr 8, 2014 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...
    – peterh
    Apr 8, 2014 at 12:10

1 Answer 1

4

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.

6
  • 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. Apr 8, 2014 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.
    – peterh
    Apr 8, 2014 at 12:40
  • @PeterHorvath col1 IN ('1','TEXT','HO TED') is equivalent to (col1='1' OR col1='TEXT' OR col1='HO TED') Apr 8, 2014 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.
    – peterh
    Apr 8, 2014 at 12:57
  • @PeterHorvath please explain about interval-query and also why you are preferring for tree-index for table 1
    – Santhosh
    Apr 8, 2014 at 13:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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