20 Practical Rules for MySQL Index Optimization

Preface

Indexes are widely known, but many developers rarely use them effectively. This article details twenty index‑optimization rules that can help you 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

Leading or full wildcard searches should be avoided; use a search engine if necessary.

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

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

UNION ALL uses the least CPU. IN generally performs similarly and is recommended.

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

3. Negative conditions 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. Composite index left‑most prefix rule

Creating a composite index on (a,b,c) automatically creates indexes on a, (a,b), and (a,b,c). Use the most selective column on the left.

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

Build a composite index (login_name, passwd) rather than (passwd, login_name).

5. Range condition columns block following columns

select * from employees.titles where emp_no < 10010 and title='Senior Engineer' and from_date between '1986-01-01' and '1986-12-31';

Only the leftmost range column can use the index; columns to its right are ignored.

6. Avoid functions or calculations on indexed columns

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

Rewrite as:

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

7. Implicit type conversion disables indexes

select * from user where phone=13800001234;

Quote the value:

select * from user where phone='13800001234';

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

High‑frequency updates on B‑tree indexes degrade performance; columns with low distinctness (e.g., gender) rarely benefit from an index.

9. Use covering indexes to avoid row lookups

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

Create a covering index (login_name, passwd, login_time) so the query can be satisfied entirely from the index.

10. Indexes do not store NULL values

Any column containing NULL is ignored in a composite index; prefer NOT NULL constraints.

11. IS NULL / IS NOT NULL cannot use indexes

These predicates force full table scans.

12. Leverage index ordering for ORDER BY / GROUP BY

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

Build a composite index (a,b,c) to avoid file‑sort.

13. Prefix (short) indexes

Index only the first N characters of a long VARCHAR column to reduce index size while maintaining selectivity.

14. Use delayed join or sub‑query for large OFFSET pagination

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

This avoids scanning all preceding rows.

15. Use LIMIT 1 when only one row is expected

select * from user where login_name=? limit 1;

Allows the engine to stop after finding the first match.

16. Avoid joining more than three tables

Ensure join columns have the same data type and are indexed; prefer indexing the right‑hand side of LEFT JOINs.

17. Keep the number of indexes per table under five

Too many indexes increase write overhead.

18. Aim for EXPLAIN type >= range (prefer ref or const)

CONST means a single row match; REF uses a normal index; RANGE uses a range scan; TYPE=INDEX indicates a full index scan and should be avoided.

19. Unique business fields must have a unique index

Even composite unique keys improve lookup speed and guarantee data integrity.

20. Common misconceptions about indexes

More indexes are not always better.

Indexes do consume space and affect write performance.

Unique constraints should not be replaced by application‑level checks.

Do not optimize prematurely without profiling.

Conclusion

The twenty principles above provide a comprehensive guide to effective MySQL index optimization.