multi column index question 1

[bastienk]

Hi All,

I have a table that uses 3 inner/outer joins to create some reports. I have been tasked to speed this up due to load on the system as the original query takes about 4 minutes to run.

Will a mutli column index based on the columns in the where clause speed things up? Or should I look at taking the query apart and run separate queries for the individual joins?




Bastien

Cat, the other other white meat

 

[TonyGroves]

Having the WHERE columns indexed is always a good idea as far as SELECT query speed is concerned; there's no harm trying it and seeing what happens. However, it could slow down UPDATE or INSERT queries, but you can always drop the indexes later if they don't improve matters.

-----
ALTER world DROP injustice, ADD peace;

 

[TonyGroves]

Clarification:

By "WHERE columns", I mean all the columns involved in the WHERE and JOIN...ON conditions.

-----
ALTER world DROP injustice, ADD peace;

 

[bastienk]

perfect. i'll try it, thanks


Bastien

Cat, the other other white meat

 

[bastienk]

One more thing, does this still have a positive effect if the columns are of high cardinality (few values) or nulls?

(one column is a yes/no) and the other is null or has value, and the search is on the null




Bastien

Cat, the other other white meat

 

[TonyGroves]

If a particular field value occurs in half or more of the records, the query optimiser might decide it would be faster to ignore an index. You can find out by creating your indexes, then running an EXPLAIN SELECT ... query, where the SELECT ... is the text of the query you are testing. The key field returned shows what indexes are being used.

-----
ALTER world DROP injustice, ADD peace;

 

[bastienk]

Took a two step approach and broke the query up and added indexes...the main query went from 4 minutes as a whole to 4.5 seconds with the sql alterations. Then I applied the composite index and dropped the time to .06 seconds.

Thats start worthy

Thanks


Bastien

Cat, the other other white meat