0

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).

Improve this question
4
  • select * is not a good idea ! – Kin Shah Apr 6 '20 at 2:28
  • Did that for brevity, not doing that in the actual query. I'm not selecting any of the items used in the WHERE filters though. – abrarisme Apr 6 '20 at 2:44
  • All indexes (clustered or non-clustered) work the same way. They are filtered by the column you put in and in the order of them. If the data is ordered by groupID then date, it means that the date are not ordered (you can have january 1st for group id 1 and then june 6th for goupid1 and february 10th for groupId2. Knowing that, it's easy to understand why you can seek to a specific date. Try building you non-clustered index with the fields in another order (ex : put the date first, then accountID and include the other field. You will probably end up with a better index. – Dominique Boucher Apr 6 '20 at 12:26
  • > Try building you non-clustered index with the fields in another order (ex : put the date first, then accountID and include the other field. We're going to end up having multiple items that we select from each date (and we'd end up having maybe 30 dates that we select from). This splits the search that we have to perform into potentially 30 different sections of the table. Is that OK (goes down multiple paths of the B-tree)? – abrarisme Apr 7 '20 at 0:02
2

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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.