Master MySQL Index Optimization: 20 Proven Rules to Boost Query Performance

Preface

Indexes are widely known, but few truly understand how to use them effectively. This article presents a set of index‑optimization principles that can be applied directly in daily work to write the most efficient SQL statements.

Index Optimization Rules

1. Leading wildcard LIKE cannot use an index

select * from doc where title like '%XX';   -- cannot use index
select * from doc where title like 'XX%';   -- can use index

Page searches must avoid left‑most wildcards; use a search engine if needed.

2. UNION, IN, OR can all use indexes – prefer IN

union

can use an index and consumes the least CPU in MySQL.

select * from doc where status=1
union all
select * from doc where status=2;

in

can use an index; its CPU cost is slightly higher than union all but negligible, so in is generally recommended.

select * from doc where status in (1,2);

or

is supported by newer MySQL versions, but its CPU cost is higher than in; frequent use is discouraged.

select * from doc where status = 1 or status = 2;

Supplement : Some claim that using or in a where clause disables the index – this is a misconception. The index can still be used if all columns in the where clause are indexed, the optimizer chooses the plan, and the server version (≥5.0) has index_merge_union=on enabled.

3. Negative conditions cannot use an index

Operators such as !=, <>, not in, not exists, not like prevent index usage.

Example:

select * from doc where status != 1 and status != 2;

Rewrite as:

select * from doc where status in (0,3,4);

4. Left‑most prefix rule for composite indexes

If a composite index is built on (a,b,c), MySQL automatically creates indexes on a, (a,b), and (a,b,c).

For a login query:

select uid, login_time from user where login_name=? and passwd=?

Create a composite index on (login_name, passwd) because login_name is frequently queried alone, while passwd alone is rarely needed.

Place the most selective column at the leftmost position of a composite index.

When mixed equality and range conditions exist, put the equality columns first. Example: where a>? and b=? – column b (equality) should be leftmost even if a has higher selectivity.

The order of columns in the WHERE clause does not need to match the index order for left‑most prefix queries.

5. Range condition columns block subsequent columns

Range operators (<, <=, >, >=, BETWEEN) can use an index, but any column to the right of a range column cannot be used.

Only one range column can be used per index.

6. Do not apply functions or calculations on indexed columns

Using functions like YEAR(create_time) <= '2016' disables the index; rewrite as create_time <= '2016-01-01'.

select * from doc where YEAR(create_time) <= '2016';

select * from doc where create_time <= '2016-01-01';

7. Implicit type conversion can cause full scans

Comparing a VARCHAR column without quotes forces MySQL to convert types and disables the index. select * from user where phone=13800001234; Rewrite as:

select * from user where phone='13800001234';

8. Frequently updated columns with low selectivity should not be indexed

Indexes on columns that change often increase B‑tree maintenance overhead.

Columns with low cardinality (e.g., gender) provide little filtering benefit.

Consider indexing only when distinct‑value ratio exceeds ~80% (use count(distinct(col))/count(*)).

9. Use covering indexes to avoid row look‑ups

If the query columns are exactly the indexed columns, MySQL can satisfy the query from the index alone, eliminating the need to read the data rows.

Select uid, login_time from user where login_name=? and passwd=?

Create a composite covering index on (login_name, passwd, login_time).

10. Indexes do not contain NULL values

Any column that can be NULL is ignored in a composite index for that row; use NOT NULL constraints and default values when possible.

11. IS NULL / IS NOT NULL cannot use an index

12. Leverage index ordering for ORDER BY / GROUP BY

Place the ORDER BY column as the last part of a composite index to avoid file‑sort.

select * from table where a=? and b=? order by c;

Define an index on (a,b,c).

If a range condition appears before the ORDER BY column, the index ordering cannot be used (e.g., WHERE a>10 ORDER BY b with index (a,b) fails).

13. Use short (prefix) indexes when appropriate

For long CHAR columns, index only the first N characters if they provide sufficient selectivity, reducing index size and maintenance cost.

14. Optimize large‑offset pagination

MySQL retrieves offset+N rows and discards the first offset rows, which is inefficient for large offsets. Use a sub‑query to fetch the primary keys first, then join back to the table.

select a.* from table1 a,(select id from table1 where condition limit 100000,20) b where a.id=b.id;

15. Use LIMIT 1 when only one row is expected

Explicitly adding LIMIT 1 lets MySQL stop scanning after the first match.

select * from user where login_name=? limit 1;

16. Avoid joining more than three tables

Ensure join columns have the same data type and are indexed, especially on the right‑hand side of a LEFT JOIN.

17. Keep the number of single‑table indexes under five

18. Aim for EXPLAIN type >= RANGE (prefer REF or CONST)

CONST: single matching row (primary key or unique index).

REF: ordinary index lookup.

RANGE: index range scan.

TYPE=INDEX indicates a full index scan and is slow.

19. Unique fields (including composite) must have a UNIQUE index

Even if application logic checks uniqueness, a UNIQUE index prevents dirty data and improves lookup speed.

20. Common misconceptions about indexes

More indexes are not always better; create them based on actual query needs.

Do not avoid indexes for fear of space or write overhead; balance is required.

Do not rely solely on application‑level checks for uniqueness.

Avoid premature optimization without understanding the workload.

Index Selectivity and Prefix Indexes

Indexes speed up queries but consume storage and affect write performance; they should be used judiciously.

Do not create indexes on very small tables (e.g., < 2,000 rows) or on columns with low selectivity.

Selectivity = Cardinality / #Rows, ranging from (0,1]. Higher selectivity means more valuable indexes. Index Selectivity = Cardinality / #T Example: title column in employees.titles has selectivity ~0.0000, so an index is unnecessary.

Prefix indexes can achieve near‑full‑column selectivity while keeping the index key short, but they cannot be used for ORDER BY, GROUP BY, or covering indexes.

By carefully choosing prefix lengths (e.g., indexing first_name and the first 4 characters of last_name), you can obtain high selectivity (≈0.90) with a much smaller index, resulting in performance gains of over 100× for name‑based lookups.