Index Definition Order and ORDER BY Clause

Question

So i was doing my morning blog reading and stumbled upon this fun exercise:

https://www.erikdarlingdata.com/sql-server/lets-design-an-index-together-part-3/

Here is the query in question from the article and the index he proposes.

SELECT TOP (5000)
       p.LastActivityDate,
       p.PostTypeId,
       p.Score,
       p.ViewCount
FROM dbo.Posts AS p
WHERE p.PostTypeId = 1
AND   p.LastActivityDate >= '20110101'
ORDER BY p.Score DESC;

CREATE INDEX whatever 
    ON dbo.Posts(PostTypeId, Score DESC, LastActivityDate) 
        INCLUDE(ViewCount) WITH (DROP_EXISTING = ON);

Pretty fun build and index and try and tune it accordingly. However, I had been previously under the possible misunderstanding that index key order matters and that certain WHERE clauses might not use certain indexes when the index key order doesnt match the query. Meaning, that my lack of experience with the particular scenario listed out, my assumed thought would be that this query wouldnt use this index because Score is in the middle of the index key definition but not in the query's where clause.

Does ORDER BY columns get evaluated when the optimizer decides what index to use and as long as the WHERE clause columns and ORDER by columns are in the index definition then itll use it?

I guess my question is more about how the optimizer evaluates indexes in regards to WHERE clauses and ORDER BY clauses.

Answer 1

The order of keys definitely matters. The two suggested indexes take a different approach to solving it.

Let’s think about how the query runs when the index is on (PostTypeID, LastActivityDate), thinking about doing it by hand.

We can quite easily find all the rows that match the WHERE clause - PostTypeID 1 and recent enough. But then we need to sort those by Score to find the top 5000. If we have a LOT of rows to sort, this could be expensive.

Alternatively, with an index on (PostTypeID, Score DESC), we can only filter to the PostTypeID, but then we can go through that data in the right order. Yes, we have to reject any rows that aren’t recent enough based on LastActivityDate, but once we’ve found the 5000 rows we care about, we can stop. We don’t have to do that expensive sort. But we’re looking through more rows than we care about. And incidentally, I don’t think there’s much value to having LastActivityDate in the key columns - it would fit just as nicely in the included columns, as it’s not playing a part in the Seek predicate. The Seek operator is simply returning all the rows for that PostTypeID value in Score order.

The Query Optimizer knows that data can be pulled from an index in the order specified within the index, and this can benefit it in many ways. Maybe it helps for a Merge Join, or a Stream Aggregate, or for an ORDER BY clause.

If we figure that PostTypeID is filtered to a single value, one index is on Score to avoid a Sort while the other is on LastActivityDate to tighten the Seek range. The QO weighs up the expected cost of each and chooses the ‘cheaper’ one.