Menu
I have an index on columns A, B, C, D of table T
I have a query that pulls from T with A, B, C in the WHERE clause.
Will the index be used or will a separate index be needed that only includes A, B, C?
735
You should index the columns that give the performance for the operations you'll use. Indexes help reads, so if you're mostly reading, index columns that will be searched on, sorted by, or joined to other tables relationally. Otherwise, it's more expensive than what benefit you may see. Save this answer.
If you have an index on every column, the cost is additional storage space and a little bit of overhead on inserts and updates. When you insert a new record into your table with 60 indexed columns, you also have to create the index entries for those 60 columns.
Indexes should not be used on small tables. Indexes should not be used on columns that return a high percentage of data rows when used as a filter condition in a query's WHERE clause. For instance, you would not have an entry for the word "the" or "and" in the index of a book.
Having too many indexes could potentially affect the overall write speed of your database and result in unneeded contention and table locking for too long. Also indexes are optimal when they cover (are defined) on the fields that are part of the predicates ( JOIN , WHERE , or HAVING clauses) of your queries.
David B is right that you should check the execution plan to verify the index is being used.
Will the index be used or will a separate index be needed that only includes A, B, C?
To answer this last part of the question, which I think is the core underlying topic (as opposed to the immediate solution), there is almost never a reason to index a subset of your indexed columns. If your index is (A, B, C, D), a WHERE against (A, B, C) will most likely result in an index seek, which is the ideal situation -- the index includes all the information the engine needs to go directly to the result set. I believe this is holds true for numeric types and for equality tests in string types, though it can break down with LIKE '%'s). On the other hand, if your WHERE only referenced D, you would most likely end up with an index scan, which would mean that the SQL engine would have to scan across all combinations of A, B, and C, and then check whether D met your criteria before deciding whether to add the row to the result set. On a particularly large table, when I found myself having to do a lot of queries against column "D", I added an additional index for D only, and saw about 90% performance improvement.
Edit: I should also recommend using the Database Engine Tuning Advisor in SQL Management Studio. It will tell you if your table isn't indexed ideally for the query you want to run.
171
It depends!
WHERE A like '%x%'
and B = 1
and C = 1
//
WHERE A = 1
OR B = 1
OR C = 1
//
WHERE DateAdd(dd, 1, A) = '2008-01-01'
AND B = 1
AND C = 1
These will not rely on the index, because the index is not useful.
Click on "display estimated execution plan" to confirm potential index usage.
33
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
