question

Fatherjack avatar image
Fatherjack asked

whether to drop an index

I have an index on a table that is `IX_Existing on JATable (CR, MC, SR) include (NF, S)` however when I look at the missing index details I am seeing that an index on the same table is suggested as `IX_Missing on JATable (SR, MC) include (CR, NF, S)`. Why isn't the existing index being used. Is it because the indexed columns are not in the same order as the ones that, presumably, the query is returning? ie CR, MC and SR but the query wants SR followed by MC? The current index is highly used for seeks so I am loathe to alter/drop it in favour of the one suggested as I would expect to see the reverse of my current situation in due course. The most selective column in the table is CR, followed by MC and then SR - hence the order of the columns in the current index.
indexingoptimization
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

WilliamD avatar image
WilliamD answered
If your query, whatever it is, queries SR and MC, but CR is nowhere to be seen the current index is pretty useless for that query. The order of the columns of an index is paramount to it being used. Think in the old adage of a phone book, searching for all the Johns can only be done with a full index scan, whereas a search for Jones can be done with an index seek (then scan), i.e. column order is important. I would suggest adding the second index (if it really needs to be there) and then monitoring system behaviour after that. It could be that the two indexes together could be of benefit to *other* queries you weren't even considering. Don't forget plan recompiles that could happen and all the rest.
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Matt Whitfield avatar image Matt Whitfield ♦♦ commented ·
Multiple indexes ftmfw. Today I made two indexes on a table, one was (location, startdate) include (enddate) and the other was (location, enddate) include (startdate). Just gotta love those seeks. Plus, scans show up in red in the query plan viewer in SQL Everywhere. Blue is so much nicer! :)
3 Likes 3 ·
WilliamD avatar image WilliamD commented ·
But don't forget, the 2nd index could be of benefit to more than one query. I would have no problem having both indexes on my system if the overhead is justifiable - in fact, I have quite a few indexes where this is the case. Either a subset of columns stored in a different order, or the same columns in a different order. The performance advantages far outweigh the maintenance overhead where I use them - nice fast merge join instead of loops join / expensive sorts (that quite often like to spill to tempdb). Don't pooh-pooh the index unless it is really to costly to maintain.
2 Likes 2 ·
Fatherjack avatar image Fatherjack ♦♦ commented ·
yeah, its back to the basics I guess, it seems such a waste to essentially duplicate the index storage for this table... May try to find the query in the cache and see if we cant re-jigger it.
0 Likes 0 ·
Håkan Winther avatar image
Håkan Winther answered
The second index That is suggested is a result of queries where the cr column is not in a where or join clause. You need both indexes for best read performance. But keep in mind that the order of the index columns may be incorrect. You should probably deity the order because of the selectivity.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.