20 Essential MySQL Index Optimization Rules Every Developer Should Know

Index Optimization Principles

1. LIKE with leading wildcard

SELECT * FROM doc WHERE title LIKE '%XX'; -- cannot use index
SELECT * FROM doc WHERE title LIKE 'XX%'; -- can use index

Avoid left‑most wildcards; use a full‑text search engine if such queries are required.

2. UNION, IN, OR

UNION [ALL]

can use an index and has the lowest CPU cost. IN also uses an index; CPU cost is slightly higher but negligible – generally preferred. OR can use an index in recent MySQL versions, but CPU cost is higher; use sparingly.

An index can still be used with OR if all referenced columns are indexed and optimizer settings (e.g., index_merge_union=ON) allow it.

3. Negative predicates

Operators such as !=, <>, NOT IN, NOT EXISTS, NOT LIKE prevent index usage.

SELECT * FROM doc WHERE status != 1 AND status != 2;
-- rewrite
SELECT * FROM doc WHERE status IN (0,3,4);

4. Composite index left‑most prefix

If an index on (a,b,c) exists, MySQL can use the prefixes (a) and (a,b) automatically.

SELECT uid, login_time FROM user WHERE login_name=? AND passwd=?;

Build a composite index (login_name, passwd) because login_name is frequently queried alone.

Place the column with the highest selectivity first. If a range predicate and equality predicates coexist, put equality columns first (e.g., WHERE a>? AND b=? → index (b,a)). The WHERE clause order does not need to match the index order, but keeping it consistent is good practice.

5. Range condition blocks columns to the right

Only the first range column in a composite index is used; columns after a range predicate are ignored.

SELECT * FROM employees.titles
WHERE emp_no < 10010 AND title='Senior Engineer' AND from_date BETWEEN '1986-01-01' AND '1986-12-31';

6. Avoid functions on indexed columns

Applying functions or calculations disables the index.

-- bad
SELECT * FROM doc WHERE YEAR(create_time) <= '2016';
-- good
SELECT * FROM doc WHERE create_time <= '2016-01-01';

Similarly, replace date <= CURDATE() with a literal date when possible.

7. Index columns with high update frequency or low selectivity

Frequent updates cause B‑tree modifications; low‑cardinality columns (e.g., gender) provide little filtering. Index a column only if selectivity > 0.8, calculated as COUNT(DISTINCT(col))/COUNT(*).

8. Covering indexes

A covering index contains all columns required by the query, eliminating row look‑ups.

SELECT uid, login_time FROM user WHERE login_name=? AND passwd=?;

Index (login_name, passwd, login_time) makes the query covering.

9. NULL handling

Columns that can contain NULL are not stored in the index; they become ineffective in a composite index. Use NOT NULL constraints and defaults.

10. IS NULL / IS NOT NULL

These predicates force a full scan because NULL values are not indexed.

11. ORDER BY / GROUP BY

If the ORDER BY columns are the trailing part of a composite index, MySQL can avoid a filesort. A preceding range condition prevents this optimization.

12. Prefix (short) indexes

For long CHAR columns, define a prefix length (e.g., first 10‑20 characters) to reduce index size while keeping high selectivity. Prefix indexes cannot be used for ORDER BY, GROUP BY, or covering indexes.

13. Pagination with large offsets

MySQL reads offset+N rows then discards the first offset. For large offsets, first fetch primary keys with LIMIT and then join back.

SELECT a.* FROM table1 a
JOIN (SELECT id FROM table1 WHERE condition LIMIT 100000,20) b ON a.id=b.id;

14. Use LIMIT 1 when only one row is expected

SELECT * FROM user WHERE login_name=? LIMIT 1;

15. Join count

Prefer no more than three tables in a join. Ensure join columns have identical data types and are indexed, especially on the right side of a LEFT JOIN.

16. Index count per table

Keep the number of indexes on a single table under five to balance maintenance cost and query performance.

17. EXPLAIN type

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

REF : ordinary index lookup.

RANGE : index range scan.

INDEX : full index scan (slow).

18. Unique indexes for uniquely identifying fields

Unique indexes add negligible insert overhead but guarantee data integrity and speed up look‑ups.

19. Common misconceptions

More indexes always improve performance.

Indexes consume too much space and drastically slow writes.

Uniqueness should be enforced only in application code.

Premature optimization without understanding the workload.

Index Selectivity and Prefix Indexes

Selectivity = Cardinality / RowCount, value in (0,1]. Higher selectivity means the index is more useful. Avoid indexes on tables with <2000 rows or on columns with low selectivity.

Example: employees.titles.title has selectivity ~0.0000, so indexing is unnecessary.

Prefix index example on employees.employees:

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

Query time dropped from 0.119 s to 0.001 s (≈120× faster). Prefix indexes cannot be used for ORDER BY, GROUP BY, or covering indexes.

Conclusion

The article presents twenty practical principles for MySQL index optimization, covering predicate handling, composite index design, covering indexes, selectivity calculation, and pagination strategies.