9 Essential MySQL Index Optimization Techniques You Must Know

1. Choose High‑Selectivity Columns for Indexes

Prefer columns with high distinct values and create unique indexes when possible; higher selectivity yields more efficient index usage.

Examples of high‑selectivity columns include name, ID number, etc., while low‑selectivity fields like gender or age are generally not beneficial to index.

2. Avoid Operations on Indexed Columns

Applying functions or arithmetic on indexed columns disables the index, causing a full table scan.

-- Full table scan
select * from article where year(publish_time) < 2019;

-- Use index
select * from article where publish_time < '2019-01-01';

-- Full table scan
select * from article where id + 1 = 5;

-- Use index
select * from article where id = 4;

3. Apply the Leftmost Prefix Rule

For composite indexes, queries must start with the leftmost column and cannot skip columns.

Example index columns: name, age, pos.

EXPLAIN SELECT * FROM staffs WHERE NAME = 'July';               -- uses index on NAME
EXPLAIN SELECT * FROM staffs WHERE NAME = 'July' AND AGE = 25; -- uses index on NAME and AGE
EXPLAIN SELECT * FROM staffs WHERE NAME = 'July' AND POS='dev';-- uses only NAME (AGE skipped)
EXPLAIN SELECT * FROM staffs WHERE AGE = 25 AND POS='dev';   -- does not use index (NAME missing)

4. Indexes Must Not Contain NULL Values

When designing multi‑column composite indexes, ensure none of the columns can be NULL; otherwise the index entry is ignored.

5. Leading Wildcard LIKE Queries Bypass Indexes

SELECT * FROM user WHERE name LIKE '%Chen%';

This pattern forces a full table scan. Use a trailing wildcard (e.g., LIKE 'Chen%') to enable index usage.

6. Avoid Inequality Operators

EXPLAIN SELECT * FROM staffs WHERE NAME <> 'July';

Using != or <> often prevents index usage, leading to full scans.

7. Quote String Literals for Character Columns

For VARCHAR columns, enclose values in quotes to allow index usage.

-- Full table scan
select * from article where id = 100;

-- Uses index
select * from article where id = '100';

8. Use LIMIT 1 When Only One Row Is Needed

Adding LIMIT 1 stops the engine after finding the first matching row, improving performance.

9. Leverage EXPLAIN to Optimize Indexes

Run EXPLAIN to view the execution plan and understand how MySQL processes queries, which indexes are used, and where bottlenecks exist.

Key points of EXPLAIN output include table read order, access type, usable indexes, actual index usage, table references, and estimated rows examined.