Practical MySQL SQL Optimization: How to Locate and Rewrite Slow Queries

Common Index Pitfalls

Indexes are ordered structures; any expression that prevents MySQL from directly comparing the indexed column value disables the index.

Implicit type conversion : When a VARCHAR column is compared to a numeric literal, MySQL casts the column to a number, breaking the index order.

SELECT * FROM orders WHERE order_no = 20240001;  -- index not used
SELECT * FROM orders WHERE order_no = '20240001'; -- index used

Functions or arithmetic on indexed columns : Wrapping an indexed column in a function or expression removes the ordered property.

SELECT * FROM orders WHERE DATE(created_at) = '2024-01-01'; -- index not used
SELECT * FROM orders WHERE created_at >= '2024-01-01' AND created_at < '2024-01-02'; -- index used
SELECT * FROM orders WHERE amount + 10 > 100; -- index not used
SELECT * FROM orders WHERE amount > 90; -- index used

Left‑most prefix violation (composite indexes) : The index can be used only if the leftmost columns are referenced.

WHERE user_id = 1 AND status = 'paid';   -- both columns used
WHERE user_id = 1;                     -- leftmost column used
WHERE status = 'paid';                 -- leftmost column missing, index not used

LIKE with leading wildcard : LIKE '%abc' forces a full scan; a prefix pattern can use the index.

WHERE order_no LIKE '2024%';   -- index used
WHERE order_no LIKE '%0001'; -- full scan

Operators != , NOT IN , IS NOT NULL : Not always index‑inefficient; the optimizer decides based on cost estimates and row selectivity.

OR with an unindexed column : The whole query may fall back to a full scan. Either add indexes on both sides (possible index_merge) or rewrite with UNION.

SELECT * FROM orders WHERE user_id = 1 OR amount > 1000; -- full scan

Covering Index (Eliminate Back‑Table Lookups)

A covering index contains all columns required by the query, so MySQL never needs to fetch the row from the clustered index. EXPLAIN shows Using index when this happens.

SELECT id, user_id, status FROM orders WHERE user_id = 1; -- uses covering index, no back‑table
SELECT * FROM orders WHERE user_id = 1;                     -- back‑table required

Avoid SELECT * unless every column is needed.

Consider adding frequently selected columns to a composite index, balancing write overhead.

Deep Pagination (Why LIMIT 100000,10 Is Slow)

MySQL must read and discard the preceding rows, giving linear cost.

Solution A – Subquery with covering index : First fetch primary keys via the index, then retrieve full rows for those keys.

SELECT * FROM orders WHERE id IN (
    SELECT id FROM orders ORDER BY created_at LIMIT 100000,10
);

Solution B – Cursor‑style pagination : Remember the last seen id and query with WHERE id > last_id LIMIT 10. Fast for sequential pages but cannot jump arbitrarily.

SELECT * FROM orders WHERE id > :last_id ORDER BY id LIMIT 10;

COUNT Variants

Behavior differs by storage engine and expression.

COUNT(*) : Counts all rows (including NULLs). Optimizer chooses the smallest usable index (usually a secondary index) and scans it. Recommended for total row count.

COUNT(1) : Equivalent to COUNT(*); no performance gain.

COUNT(primary_key) : Scans the clustered index; result equals COUNT(*) but usually slower because the clustered index is larger.

COUNT(col) : Counts only non‑NULL values of col. If col lacks an index, a full table scan may occur.

When COUNT(*) is still slow on large tables, possible mitigations:

Force a covering index, e.g., SELECT COUNT(*) FROM orders WHERE user_id = 1 using idx_user_status.

Use an approximate estimate from SHOW TABLE STATUS or information_schema.TABLES ( TABLE_ROWS).

Maintain a separate counter table or a Redis key for frequent exact counts.

ORDER BY and Filesort

Two ways to obtain ordered results:

Use an index that matches the ORDER BY clause – fast, no Using filesort.

Let MySQL sort the result set – shown as Using filesort in EXPLAIN, slower.

SELECT * FROM orders ORDER BY created_at LIMIT 10; -- idx_created, no filesort
SELECT * FROM orders ORDER BY amount LIMIT 10;      -- no index on amount, filesort

Guidelines:

Build an index that covers the ORDER BY columns, possibly combined with WHERE columns (equality columns first, sorting columns after).

Avoid SELECT * when sorting large result sets; fewer columns reduce memory usage.

Adjust sort_buffer_size cautiously for large sorts.

JOIN Optimization (Small Table Drives Large Table)

MySQL provides three join algorithms (InnoDB):

Nested‑Loop Join (NLJ) : For each row of the driver table, look up matching rows in the driven table. Efficient when the driven table’s join columns are indexed.

Block Nested‑Loop (BNL) : If the driven table lacks an index, MySQL buffers rows from the driver table in join_buffer and compares in blocks. Less efficient than NLJ.

Hash Join (MySQL 8.0.18+) : Default for equality joins that cannot use an index; faster than BNL. EXPLAIN shows Using join buffer (hash join).

Key optimization points:

Ensure join columns of the driven table have indexes; otherwise Using join buffer appears in EXPLAIN.

Prefer the filtered (small) table as the driver. STRAIGHT_JOIN can force the order if needed.

Match data types of join columns; mismatched types cause implicit conversion and index loss.

How to Locate Slow SQL

Enable the slow‑query log and set long_query_time (e.g., 1 s).

SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;

Analyze the log with mysqldumpslow or pt‑query‑digest to identify the most expensive statements.

Run EXPLAIN (or EXPLAIN ANALYZE on 8.0.18+) on the identified queries. Check type (e.g., ALL for full scan), key (whether an index is used), rows (estimated rows scanned), and Extra for Using filesort or Using temporary.

Optimization Checklist

Index Level

Create indexes on columns used in WHERE, JOIN, ORDER BY, and GROUP BY. Do not index columns that appear only in the SELECT list.

Prioritize high‑cardinality columns; low‑selectivity columns alone rarely benefit from indexing.

In composite indexes, place the most selective equality columns first (left‑most prefix).

Use prefix indexes for long strings; design covering indexes when possible to avoid back‑table lookups.

Remove unused indexes to reduce write overhead.

SQL Writing Level

Keep condition types consistent with column types to avoid implicit conversion.

Do not wrap indexed columns in functions or arithmetic expressions.

Avoid SELECT *; select only needed columns.

For deep pagination, use cursor‑style WHERE id > ? instead of large OFFSET.

Use COUNT(*) for total row counts.

Ensure join columns are indexed, have matching types, and that a small filtered table drives the join.

Method Level

Base decisions on EXPLAIN output; avoid absolute rules about index usage.

First locate slow queries via the slow‑query log, then analyze with EXPLAIN or EXPLAIN ANALYZE, and finally apply targeted rewrites.