Composite indexes – Does column order matter?

When it comes to creating composite indexes, a common bit of advice we’re given is to put the most selective column first.  And I’ve always just taken this at face value.  But recently I got to thinking, is this still true?  I’d never actually tested this theory myself.  Does this rule still apply?

So let’s take a look.  I started by creating a test table in AdventureWorks using existing data.  My goal was to create a table with one high-cardinality column and at least one very low-cardinality column.

USE AdventureWorks;
GO

SET NOCOUNT ON

CREATE TABLE myBigTable (
ID int IDENTITY(1,1),
SURNAME VARCHAR(40),
LOCATION CHAR(3))

INSERT INTO myBigTable (SURNAME, LOCATION)
SELECT LastName, StateProvinceCode FROM Person.Contact CROSS JOIN Person.StateProvince ;
GO

SELECT    COUNT(distinct ID) AS DistinctIDs,
 COUNT(DISTINCT surname) AS DistinctNames,
 COUNT(DISTINCT location) AS DistinctLocs
FROM myBigTable;
GO

Perfect.  ID is unique and location is very low cardinality.  I’ll use those 2 columns in my test indexes.

CREATE INDEX i_highcard ON myBigTable (ID, location);  -- high cardinality first
CREATE INDEX i_lowcard ON myBigTable (location, ID);    -- low cardinality first
GO

Before we go any further, let’s take a look at the physical size of the indexes, just to see if there’s any difference there.

select name, index_id from sys.indexes where object_id = OBJECT_ID('myBigTable')
SELECT * FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID('myBigTable'), NULL , NULL, 'LIMITED');
GO

Judging by the fragment_count and page_count values, I’d say there’s no difference in size.  Now, on to query performance.  First we’ll query the table for a particular ID value.  I’ll use the INDEX hint to force the optimizer to use each index.

SET STATISTICS IO ON
SELECT * FROM myBigTable WITH (INDEX = i_highcard) WHERE ID = 77321
SELECT * FROM myBigTable WITH (INDEX = i_lowcard) WHERE ID = 77321

Check out those execution plans.  When we query based on the first column in the index, the optimizer is able to do an index seek.  But when we query based on the second column in the index, as in the second query, the optimizer doesn’t know where to enter the index, so it’s forced to do an index scan.  (In fact, the optimizer even suggests we create an index on ID.)  We can also see the impact of these different execution plans in the IO statistics.

From the first query:
Table ‘myBigTable’. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

And from the second query:

Table ‘myBigTable’. Scan count 5, logical reads 9563, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Worktable’. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

That’s a lot more IO the database has to do.  And we all know, when it comes to IO, less is more.

Now, those results were pretty predictable.  But what about when both indexed columns are in the where clause?  Based on what I’ve been told, the index with the high-cardinality column first will be more efficient.  But I want to see that for myself.

SELECT * FROM myBigTable WITH (INDEX = i_highcard) WHERE ID = 77321 AND LOCATION = '04'
SELECT * FROM myBigTable WITH (INDEX = i_lowcard) WHERE ID = 77321 AND LOCATION = '04'

First, the IO stats:

Table ‘myBigTable’. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘myBigTable’. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Exactly the same.  What about the execution plans?

Also the same.  Even down to the cost.

That surprises me.  I really thought the i_highcard index would be more efficient.  So, I’m back to my original question, when it comes to composite indexes, does column order matter?  And I guess the answer is:  it depends.  SQL Server only maintains statistics on the first column in an index, so having a highly selective first column can be more efficient if you’re using that column in your WHERE clauses.  But if you’re using both columns as filter criteria, it doesn’t seem to matter.  This another reason is why it’s extremely important to know your application and how it accesses the data.

Also recommended: