about indexes

starstarstarstarstarstarstarstarstarstar Rating: 0/5 (0 vote cast) print

Hi,


On an existing table lets say columns A and B have 2 indexes.


One is on column A and one on columns A, B. I found this situation on an existing table, and I thought that the index on column A is somehow redundant (and I removed it). I think they were created with Index Tunning Wizard, and nobody checked what the wizard made.


Any other opinions? :)

 : MisterL     Reply  

Replies (3)

profile
KINGSLEY TAGBO

It looks like you are forcing your conclusions a bit. After you delete the index on column B, it has no other option but to use the index on Column A, B. Why don't you add an index on column A, add an index on column B, and also have an index on columns A,B and then see which of the three indexes it will use.


profile
KINGSLEY TAGBO

You may not be sure until you use a tool to analyze the scenarios. You should consider using the 'Show Execution Plan' menu in query analyzer o rusing SQL Profiler.

However, you may add a seperate index to column B and remove the index on both columns A and B. That way if a query covers both cloumns A and B, the individual indexes on A and B will be used.

The best way to evaluate these scenarios is still to drop an index, run a query and analyze it using one of the tools I mentioned. Then add the index back, drop another index, run the query analyze it and repeat the actions of dropping indexes, adding indexes and analyzing them.


Yes, I used 'Show Execution Plan' and for a query that used the index on column B, after deleting it used the index on columns A, B. But I don't know if that is a guarantee that it will be like this for every query.

: MisterL    Reply


Post A Reply

 Questions & Answers