CODEWITHSUNDEEP
sqldatabase
japancheese
japancheese

Reputation: 357

Order of index on multiple columns

Just a simple question, but does the order of your index matter when it spans over multiple columns?

For example, taking the query:

SELECT * FROM my_table WHERE (column_1 = 1 AND column_2 = 2)

If I want to add an index for a query like this, does it matter if my index is created like this:

CREATE INDEX my_index
ON my_table (column_1, column_2)

Or like this:

CREATE INDEX my_index
ON my_table (column_2, column_1)

Thanks for the help!

Upvotes: 22

Views: 8065

Answers (3)

Mark Byers
Mark Byers

Reputation: 838106

The best way to find out is to measure it. Try one, measure the performance, then remove that index and try the other. Typically you want data that is queried together to lie close together in the index on the disk, and the order of the columns in the index makes a difference to the way the index is stored on disk. It's very difficult to guess exactly what combination of indexes will work best, so try a few different possibilities and measure to find out which is best for your data.

Upvotes: 0

Adriaan Stander
Adriaan Stander

Reputation: 166376

This shoudl give you a good idea

SQL Server Clustered Index - Order of Index Question

There are many more like this on SO.

Upvotes: 2

Andomar
Andomar

Reputation: 238078

In the example you give, the column order does not matter.

It would matter if you order on a column; an index on (col1,col2) can be used for ORDER BY col1, col2 but not for ORDER BY col2, col1.

For WHERE clauses, an index on (col1, col2) works for WHERE col1 = 1 AND col2 = 1. It also works for WHERE col1 = 1. But it can't help with WHERE col2 = 1.

Upvotes: 24

Related Questions