Question :
I have a table filled with a bunch of data, and I want to use the following clustered index:
[account_id, group_id, run_id, page_id, date]
For a non-clustered index, if I were to to use WHERE filters involving account_id
, page_id
, and date_time
, I know that the index won’t work because I’m not also including group_id
and run_id
. Does the same thing apply when using a clustered index? I often want to make a query in the form:
SELECT *
FROM my_table
WHERE ad_account_id = %d
AND page_id = %d
AND date >= DATE(%s)
AND date <= DATE(%s)
But sometimes I have queries such as:
SELECT *
FROM my_table
WHERE ad_account_id = %d
AND group_id = %d
AND date >= DATE(%s)
AND date <= DATE(%s)
or
SELECT *
FROM my_table
WHERE ad_account_id = %d
AND group_id = %d
AND run_id = %d
AND date >= DATE(%s)
AND date <= DATE(%s)
It’s not obvious whether any of these take advantage of the clustered index past the one given on the account_id
(I’m pretty sure this isn’t doing a full table scan but even there not sure).
Answer :
Just think left-to-right, until you hit a gap.
For Q1, SQL server can search for ad_account_id (assuming that was in the index and you had a typo in the index definition), and then scan those with that value.
For Q2, SQL server can search for ad_account_id and group_id and then scan those.
For Q3, SQL server can seek for all columns in the WHERE clause.
So, yes, the same principals applies to a clustered index as for a non-clustered index.