20 Principles for MySQL Index Optimization

Introduction

Indexes are widely known, yet many developers do not use them correctly. This article details twenty practical principles for MySQL index optimization, providing concrete examples and SQL snippets that can be applied directly in daily work.

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

Left‑most or full wildcard searches should be avoided; use a search engine if necessary.

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

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

UNION ALL is CPU‑light, but IN is generally recommended for readability and comparable performance.

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

3. Negative conditions (!=, <>, NOT IN, NOT EXISTS, NOT LIKE) cannot use indexes

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

Rewrite as an IN query:

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

4. Left‑most prefix rule for composite indexes

If a composite index is created on (a,b,c), MySQL automatically builds indexes on a, (a,b) and (a,b,c). Queries should use the leftmost columns of the index.

5. Range conditions invalidate following columns

Only one range column can be used efficiently; columns to the right of a range condition are ignored.

6. Avoid functions or calculations on indexed columns

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

Rewrite to compare the raw column value:

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

7. Implicit type conversion can disable indexes

select * from user where phone=13800001234;

Quote string literals to keep the index usable:

select * from user where phone='13800001234';

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

High‑update columns (e.g., gender) add overhead without meaningful filtering; index only when selectivity exceeds ~80%.

9. Use covering indexes to avoid row look‑ups

If the query columns are fully contained in the index, MySQL can return results without accessing the table rows.

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

A composite index on (login_name, passwd, login_time) makes this a covering index.

10. NULL values are not stored in indexes

Columns that may contain NULL should be defined NOT NULL with a default value if they are part of an index.

11. IS NULL / IS NOT NULL cannot use indexes

These predicates force a full table scan.

12. Leverage index ordering for ORDER BY / GROUP BY

Place the ORDER BY column as the last part of a composite index (a,b,c) to avoid filesort.

13. Prefix (short) indexes

Index only the first N characters of long VARCHAR columns when the prefix provides sufficient selectivity, saving space and I/O.

14. Pagination optimization with delayed join

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

Fetch only the needed IDs first, then join back to the full rows.

15. Use LIMIT 1 when only one row is expected

select * from user where login_name=? limit 1;

This tells MySQL to stop scanning after the first match.

16. Avoid joining more than three tables when possible

Ensure join columns have matching data types and appropriate indexes, preferably on the right‑hand side of LEFT JOINs.

17. Keep the number of indexes per table reasonable (≤5)

Too many indexes increase write overhead.

18. EXPLAIN type should be at least RANGE, preferably REF or CONST

CONST means a unique lookup; REF uses a normal index; RANGE uses a range scan.

19. Unique fields (even composite) must have UNIQUE indexes

Unique indexes guarantee data integrity and fast look‑ups.

20. Common misconceptions about indexes

More indexes are not always better.

Indexes do consume space and slow writes; balance is needed.

Uniqueness should be enforced by the database, not only by application logic.

Avoid premature optimization without profiling.

Index Selectivity and Prefix Indexes

Index selectivity = Cardinality / total rows. High selectivity (close to 1) makes an index valuable. Low‑selectivity columns (e.g., gender) rarely benefit from indexing.

Prefix indexes can achieve high selectivity with shorter keys, but they cannot be used for ORDER BY, GROUP BY, or covering indexes.

ALTER TABLE employees.employees
ADD INDEX `first_name_last_name4` (first_name, last_name(4));

Testing shows query time improvement of over 100× after adding the prefix index.

Conclusion

The article presents twenty index‑optimization principles that, when applied, can dramatically improve MySQL query performance.