Master MySQL Slow Query Optimization: Practical Indexing Techniques

Background

In large‑scale MySQL 5.7 deployments, accumulated slow queries degrade user experience. Optimizing these queries requires systematic index design, correct index usage, and efficient SQL writing.

2. Slow‑Query Optimization

2.1 Correct Index Creation

Indexes should target high‑selectivity columns and avoid low‑selectivity ones. Over‑indexing harms write performance and consumes storage.

Do not index columns that filter only a tiny fraction of rows (e.g., a logical‑delete flag with few true values).

Place the most selective column first in a composite index, or create a single‑column index if it is highly selective.

Use covering indexes when the query still returns many rows after filtering; include all needed columns in the index to eliminate back‑table look‑ups.

Examples:

-- Low‑selectivity columns should be omitted
SELECT * FROM my_table WHERE col_a=1 AND col_b=1;
-- Composite index with high‑selectivity column first
CREATE INDEX idx_cba ON my_table(col_c, col_b, col_a);
-- Covering index for aggregation
CREATE INDEX idx_abc ON my_table(col_a, col_b, col_c);
SELECT SUM(col_c) FROM my_table WHERE col_a=1 AND col_b=1;

2.2 Correct Index Usage

Even a well‑designed index can be bypassed. Common failure patterns:

Left‑most prefix rule : Composite indexes are matched left‑to‑right. To use col_b in INDEX(col_a,col_b), the query must also reference col_a.

SELECT * FROM my_table WHERE col_b=1;  -- does NOT use the index

Implicit type conversion : Mismatched literal types (e.g., numeric literal for a VARCHAR column) prevent index use.

SELECT * FROM my_table WHERE col_a=1;   -- col_a is VARCHAR

IN + ORDER BY blocks index sorting. Adjust the index order or query to keep the sort covered.

SELECT * FROM my_table WHERE col_a IN (1,2) ORDER BY col_b;

Range queries should appear last in a composite index.

<code>SELECT * FROM my_table WHERE col_a > '2021-12-01' AND col_b=10;</code>
-- Use INDEX(col_b, col_a) instead of INDEX(col_a, col_b)

Leading‑wildcard LIKE cannot use B‑tree indexes; use full‑text search or external engines for patterns like %name%.

SELECT * FROM my_table WHERE col_b LIKE '%name%';

OR conditions may trigger index merge but often degrade performance; separate indexes on each column are preferable.

SELECT * FROM my_table WHERE col_a=1 OR col_b='';

Functions or arithmetic on columns disable index usage.

SELECT * FROM my_table WHERE DATE_SUB(CURDATE(),INTERVAL 7 DAY) <= DATE(col_b);
SELECT * FROM my_table WHERE col_b+1=10;

NOT IN / != predicates usually force full scans.

SELECT * FROM my_table WHERE col_b NOT IN (1,2);
SELECT * FROM my_table WHERE col_b != 1;

Low selectivity leads MySQL to ignore the index and perform a table scan.

Mixed ASC/DESC in ORDER BY breaks index ordering; avoid mixing directions.

2.3 Writing SQL the Right Way

Deep pagination (large OFFSET) forces MySQL to scan many rows before returning the desired page. Use keyset pagination instead:

# Keyset pagination using primary key
SELECT * FROM my_table WHERE id > last_seen_id LIMIT 100;

Alternatively, fetch only the primary keys first and join later to reduce back‑table look‑ups:

SELECT * FROM my_table t1
JOIN (SELECT id FROM my_table WHERE col_c=1 LIMIT 1000,100) t2 ON t1.id=t2.id;

Covering indexes allow MySQL to satisfy the query from the index alone, eliminating row look‑ups.

Order by primary key works because InnoDB secondary indexes store the primary key. Ensure the index is not broken by preceding predicates.

SELECT * FROM my_table WHERE col_a=1 AND col_b=2 ORDER BY id;

3. Analyzing Slow Queries

Use EXPLAIN to verify index usage. Important columns: type – join type. key – index chosen. rows – estimated rows examined. extra – additional info such as Using index condition (ICP) or Using where. Using index condition indicates that predicates are evaluated in the storage engine (ICP), reducing row fetches. Using where means filtering occurs after the index scan, causing extra back‑table reads.

4. Conclusion

Effective MySQL performance relies on three principles: useful indexes, usable indexes, and efficient queries . Build appropriate indexes, avoid patterns that invalidate them, and write SQL that can fully exploit those indexes to prevent slow‑query problems before they appear in production.