by Markus Winand.

Most Selective First

Every time a compound index is created, the order of the columns must be chosen wisely. Concatenated Indexes is devoted to this question.

However, there is the myth that you should always put the most selective column to the first position; that is just wrong.


The most important consideration when defining a concatenated index is how to choose the column order so it can be used as often as possible.

After that, there are even reasons to put the least selective column first. The Oracle database can, for example, use an INDEX SKIP SCAN in that case. But that’s an advanced feature. The most important factor...uhm, did I say that before?

The true essence of the myth is related to indexing independent range conditions—that is the only case where the selectivity should influence the index design (see Index Merge).

On my Own Behalf

I offer training, tuning and consulting. Buying my book “SQL Performance Explained” (from €9.95) also supports my work on this website.

The myth is extraordinarily persistent in the SQL Server environment and appears even in the official documentation. The reason is that SQL Server keeps a histogram for the first index column only. But that means that the recommendation should read like “uneven distributed columns first” because histograms are not very useful for evenly distributed columns anyway.

I’m not the first to fight this myth. Here are some more references that disproof the myth:

Don’t automatically put the most selective term first in a concatenated index.

— Guy Harrison in “Oracle Performance Survival Guide

One of the often-quoted fairy-tales about indexes was the directive to “put the most selective column first”. It was never a sensible rule of thumb (except, possibly, prior to version 6.0).

— Jonathan Lewis in “Oracle Scratchpad

It’s useless to have the most selective column of the index on the left if very few queries filter on it. Queries that don’t filter on it, but do filter on the other columns of the index will have to scan, and scans are expensive.

— Gail Shaw in ”SQL (Server) in the Wild

About the Author

Photo of Markus Winand

Markus Winand provides insights into SQL and shows how different systems support it at Previously he made, which is still actively maintained. Markus can be hired as trainer, speaker and consultant via

Buy his Book

Cover of “SQL Performance Explained”: Squirrel running on grass

The essence of SQL tuning in 200 pages

Buy now!
(paperback and/or PDF)

Paperback also available at

Hire Markus

Markus offers SQL training and consulting for developers working at companies of any size.
Learn more »

Do not use offset for pagination

Learn why

Visit my sibling!A lot changed since SQL-92!

The Use The Index, Luke! mug

Stickers, coasters, books and coffee mugs. All you need for learning.

Shop now

Connect with Markus Winand

Markus Winand on LinkedInMarkus Winand on XINGMarkus Winand on Twitter
“Use The Index, Luke!” by Markus Winand is licensed under a Creative Commons Attribution-Noncommercial-No Derivative Works 3.0 Unported License.
Legal | Contact | NO WARRANTY | Trademarks | Privacy and GDPR | CC-BY-NC-ND 3.0 license