Does a 4-column composite index benefit a 3-column query?

user_4926174 0
Viewed 217

I have a table with columns A, B, C, D, E, ..., N. with PK(A). I also have a composite, unique, non-clustered index defined for columns D, C, B, A, in that order.

If I use a query like: 

where D = 'a' and C = 'b' and B = 'c'

without a clause for A, do I still get the benefits of the index?

indexing sql-server sql database
3 Answers

Yeah, it will still use the index properly and make your query more efficient.

Think of it like a nested table of contents in a text-book:

  • Skim till you see the chapter name (e.g. "Data Structures")
    • Skim until you see the relevant section title ("Binary Trees")
      • Skim until you see the relevant topic (e.g. "Heaps").

If you only want to get to the binary trees section, the contents going to the topic level doesn't hurt you at all :).

Now... if you wanted to find binary trees and you didn't know they were a data structure, then this table of contents wouldn't be very useful for you (e.g. if you were missing "D").

user_8579940

Yes, SQL server can perform a seek operation on the index (D, C, B, A) for these queries:

WHERE D = 'd'
WHERE D = 'd' AND C = 'c'
WHERE D = 'd' AND C = 'c' AND B = 'b'
WHERE D = 'd' AND C = 'c' AND B = 'b' AND A = 'a'

And it could perform a scan operation on the said index for these:

WHERE C = 'c' AND B = 'b'
WHERE A = 'a'
-- etc

But there is one more thing to consider: the order of columns inside indexes matters. It is possible to have two indexes (D, C, B, A) and (B, C, D, A) perform differently. Consider the following example:

WHERE Active = 1 AND Type = 2 AND Date = '2019-09-10'

Assuming that the data contains two distinct values for Active, 10 for Type and 1000 for Date, an index on (Date, Type, Active, Other) will be more efficient than (Active, Type, Date, Other).

You could also consider creating different variations of the said index for different queries.

PS: if column A is not used inside the WHERE clause then you can simply INCLUDE it.

user_870150

Yes, now SQL can do a seek to just those records, instead of having to scan the whole table.

In addition:

  1. SQL will have better statistics (SQL will auto-create statistics on the set of columns composing the index) as to how many rows are likely to satisfy your query
  2. The fact that it is UNIQUE will also tell SQL about the data, which may result in a better plan (for example if you do DISTINCT or UNION, it will know that those columns are already distinct).
  3. SQL will have to read less data, because instead of having to read all "N" columns (even though you only need 3), it can read the index, which will have 4, so only one will be "superfluous".

Note that because the particular query in question is WHERE on D, C, and B, in this case the order of the index won't matter. If the WHERE clause is only on C and B, you would get much less benefit because SQL would no longer be able to seek on D, and (1) and (2) above wouldn't apply. (3) still would, though.

user_1558920
Related