Understanding Index Selectivity, Filtering, and Order‑By Optimization in MySQL

Preface : Before releasing database schema changes, the author (a DBA at Youzan) shares common gaps developers have in index design, aiming to record typical cases and spark discussion.

Selectivity and Filtering : A dialogue illustrates that developers often avoid low‑selectivity columns, assuming they are unsuitable for indexes. Scenario 1 shows a single‑column index on a (10 × 10⁴ rows) forces scanning many rows before filtering by b , while a composite index idx_ab(a,b) can directly locate the 1 000 matching rows, dramatically improving performance. Scenario 2 demonstrates that when data volume is small, the difference is negligible.

Tips :

Indexes should aim to locate rows that satisfy the WHERE clause as early as possible, reducing unnecessary table lookups.

When using composite indexes, consider the actual row count that each column filters, not just its theoretical selectivity.

Index Orderliness : Developers often add ORDER BY columns to composite indexes, yet still see filesort . This occurs when the order of columns in the index does not match the ORDER BY sequence, or when preceding columns are range queries. The article provides a hands‑on example:

create table x(id int not null auto_increment primary key, a int, b int, key idx(a,b));
insert into x(a,b) values(1,8),(1,6),(1,3),(2,1),(2,2),(2,4),(3,7),(3,9);

Querying with an equality on a and ordering by b uses the index without extra sorting:

select * from x where a=2 order by b;

However, a range condition on a forces using filesort because the index order cannot be fully leveraged:

select * from x where a>=1 and a<3 order by b;

The output demonstrates that the stored order of (a,b) differs from the result order, causing the additional sort step, especially noticeable with larger data sets.

Conclusion : The article summarizes two high‑frequency scenarios—misjudging low‑selectivity columns and mismatched index order for ORDER BY —and encourages developers to design more reasonable indexes based on actual data distribution and query patterns.