SQL Server Index column order performance optimization for order by

Question

I have this query:

select top 500 * from clicks 
where domainid = 'AC8BBCA4-1CC4-456E-9A85-E7A5C0D7E981' 
order by clickedOn

I wonder which index will perform better?

CREATE NONCLUSTERED INDEX [IX_p2p] ON [dbo].[clicks]
(
    [domainId],
    [clickedOn] ASC
)

Or This?

CREATE NONCLUSTERED INDEX [IX_p2p] ON [dbo].[clicks]
(
    [clickedOn] ASC,
    [domainId]
)

I would suggest to review query execution plan. It should give you clues what's better. It is also good to generate e.g. 1M of records into the table and see the difference. — Pavel Sem
– Pavel Sem, Commented Apr 4, 2020 at 14:56
I'd guess the first one. But you can just check the plans for both and see for yourself. — sticky bit
– sticky bit, Commented Apr 4, 2020 at 14:56
@mybrave I am interested in the theoretical aspect of the question. — realPro
– realPro, Commented Apr 4, 2020 at 14:57
Theoretically, index 2 won't be used at all. Because for the kind of indexing it provides, scanning the entire primary key is no slower, and because you select *, it's even faster due to no lookups. — GSerg
– GSerg, Commented Apr 4, 2020 at 14:57
At first you do filtering - index can help you to get quickly the records with same domainID. Then there is sorting. So first looks better. — Pavel Sem
– Pavel Sem, Commented Apr 4, 2020 at 15:00

Martin Smith · Accepted Answer · 2020-04-04 16:07:04Z

2

The first index is clearly better than the second one for this query.

In the first case it can seek into the exact domainid and then read the first 500 rows, which are already in clickedOn order, and then stop (plus 500 lookups to get the other column values if the table has more than the two columns shown).

The second index might be useful for the query but only in very limited circumstances. It avoids the need to sort by clickedOn but does require scanning the index in order and stopping after the first 500 rows matching the domainId are found. If you are very lucky then the first 500 rows in clickedOn order also match the domainId predicate so it too only has to read 500 rows. But chances are this won't be the case. In the worst case there aren't even 500 matching rows to be found and it needs to read the whole index. It too needs to do the 500 lookups to get the other column values.

If the table is relatively small then neither index may be used as SQL Server may decide scanning a covering index and sorting is cheaper than the 500 lookups.

answered Apr 4, 2020 at 16:07

Martin Smith

457k97 gold badges777 silver badges887 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

x-yuri Over a year ago

scanning a covering index and sorting is cheaper than the 500 lookups Is that really what you meant to say? You seem to mean that there might be a case where a full table scan can be cheaper than using any of the 2 indexes. Using an index means scanning the index and optionally (table) lookups. But in that phrasing you seem to compare operations that make up using an index, not different cases (using index #1, using index #2, table scan).

Collectives™ on Stack Overflow

SQL Server Index column order performance optimization for order by

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related