How important is the order of columns in indexes?

by

Look at an index like this:

Cols
  1   2   3
-------------
|   | 1 |   |
| A |---|   |
|   | 2 |   |
|---|---|   |
|   |   |   |
|   | 1 | 9 |
| B |   |   |
|   |---|   |
|   | 2 |   |
|   |---|   |
|   | 3 |   |
|---|---|   |

See how restricting on A first, as your first column eliminates more results than restricting on your second column first? It’s easier if you picture how the index must be traversed across, column 1, then column 2, etc…you see that lopping off most of the results in the fist pass makes the 2nd step that much faster.

Another case, if you queried on column 3, the optimizer wouldn’t even use the index, because it’s not helpful at all in narrowing down the result sets. Anytime you’re in a query, narrowing down the number of results to deal with before the next step means better performance.

Since the index is also stored this way, there’s no backtracking across the index to find the first column when you’re querying on it.

In short: No, it’s not for show, there are real performance benefits.

More Related Contents:

  1. Is it possible to write single query on two table which are not connected to each other?
  2. Get top 1 row of each group
  3. How can I do an UPDATE statement with JOIN in SQL Server?
  4. SQL update query using joins
  5. How to update two tables in one statement in SQL Server 2005?
  6. Referring to a Column Alias in a WHERE Clause
  7. SQL query to select dates between two dates
  8. Create a date from day month and year with T-SQL
  9. Selecting COUNT(*) with DISTINCT
  10. Creating an index on a table variable
  11. How to return multiple values in one column (T-SQL)?
  12. Generate a resultset of incrementing dates in TSQL
  13. Getting result of dynamic SQL into a variable for sql-server
  14. How to get difference between two rows for a column field?
  15. Better techniques for trimming leading zeros in SQL Server?
  16. SQL – Subtracting a depleting value from rows
  17. What’s the best way to select the minimum value from several columns?
  18. SELECT FOR UPDATE with SQL Server
  19. varbinary to string on SQL Server
  20. SQL Server 2005 ROW_NUMBER() without ORDER BY
  21. How to split string using delimiter char using T-SQL?
  22. Can an INNER JOIN offer better performance than EXISTS
  23. OPENXML with xmlns:dt
  24. SQL server select distinct rows using most recent value only
  25. Eliminate and reduce overlapping date ranges
  26. Cumulative Total in MS Sql server [duplicate]
  27. Use variable with TOP in select statement in SQL Server without making it dynamic [duplicate]
  28. Compare dates in T-SQL, ignoring the time part
  29. Sql Server : How to use an aggregate function like MAX in a WHERE clause
  30. Memory effective way to read BLOB data in C#/SQL 2005

Leave a Comment