12 Common MySQL Slow‑Query Triggers and Practical Fixes

1. Missing Index

When a query lacks an appropriate index, MySQL performs a full table scan. Adding the missing index (e.g., ALTER TABLE user_info ADD INDEX idx_name (name);) forces the optimizer to use the index and dramatically improves performance.

2. Index Not Effective

Even if an index exists, it may become ineffective in several scenarios:

Implicit type conversion: Comparing a varchar column to a numeric literal causes MySQL to convert the column to a number, bypassing the index. Enclosing the numeric value in quotes restores index usage.

OR conditions: Queries like SELECT * FROM user WHERE userId = 'A' OR age = 20; may trigger a full‑table scan because the optimizer cannot use the index on userId for the OR part.

LIKE with leading wildcard: Patterns such as WHERE userId LIKE '%123' prevent index usage; moving the wildcard to the end ( LIKE '123%') allows the index to be used.

Composite index left‑most rule: In a composite index (user_id, name), a condition on name alone does not use the index. The query must reference the leftmost column first.

Built‑in functions on indexed columns: Using functions like DATE_ADD(login_time, INTERVAL -1 DAY) on an indexed column disables the index. Rewrite the condition to apply the function to the constant side instead.

Column arithmetic: Expressions such as age + 1 = 30 on an indexed column break index usage; compute the value in application code.

Inequality and NOT IN: Conditions like age != 30 or age NOT IN (1,2,3) often cause the optimizer to skip the index, especially when it predicts scanning many rows.

IS NULL / IS NOT NULL: These checks may or may not use the index depending on data distribution; always verify with EXPLAIN.

3. Deep Pagination (LIMIT)

Using LIMIT offset, count with a large offset forces MySQL to scan offset + count rows, discarding the first offset rows. This increases I/O and back‑table lookups. Two common mitigations are:

Tag‑record method: Remember the last primary‑key value from the previous page and query WHERE id > last_id LIMIT count, which uses the primary‑key index.

Delayed‑join method: First fetch primary‑key IDs with the limit, then join back to the main table on those IDs, reducing back‑table work.

4. Very Large Single Tables

When a table reaches tens of millions of rows, B‑Tree depth grows, increasing disk I/O per lookup. Typical InnoDB page size is 16 KB; a two‑level B‑Tree can store roughly 18 720 rows of 1 KB each, while a three‑level tree handles ~21 902 400 rows. Beyond that, consider sharding (horizontal) or vertical partitioning, but be aware of transaction, cross‑database, sorting, pagination, and distributed‑ID challenges.

5. Excessive JOINs or Subqueries

More than three table joins often degrade performance, especially if join columns lack indexes. MySQL employs two join algorithms:

Index Nested‑Loop Join: Uses indexes on the inner table; efficient for small driving tables.

Block Nested‑Loop Join: Scans the inner table into a join buffer when no index is available, which can be costly for large datasets.

When possible, rewrite subqueries as joins, limit the number of joined tables, and ensure join columns are indexed.

6. Too Many IN Elements

IN lists with hundreds or thousands of values can cause full scans or memory pressure. Best practice: keep IN lists under ~500 elements, split large lists into batches, or replace with temporary tables.

7. Dirty‑Page Flushing

Updates modify in‑memory pages and write redo logs. When the redo log fills or the buffer pool needs space, MySQL flushes dirty pages to disk, which can stall write throughput. Regular checkpointing and adequate buffer‑pool size mitigate this.

8. ORDER BY Filesort

If the ORDER BY column is not covered by an index, MySQL performs a filesort, which may use in‑memory buffers or temporary disk files. Optimizations include adding suitable indexes, increasing sort_buffer_size, or rewriting queries to retrieve rows in the desired order directly.

9. Lock Waits

Long‑running queries may block on row or table locks. Use SHOW PROCESSLIST to identify waiting threads and consider lock‑friendly designs (e.g., shorter transactions, appropriate isolation levels).

10. DELETE … IN Not Using Index

MySQL can transform SELECT … IN (subquery) into a semi‑join that uses indexes, but it does not apply the same rewrite for DELETE … IN (subquery). Consequently, DELETE statements may fall back to full scans. Split the operation into a SELECT to fetch IDs, then DELETE by primary key.

11. GROUP BY Using Temporary Tables

GROUP BY often creates an in‑memory temporary table and may sort the result. Large groups can spill to disk, causing slowdown. Mitigations:

Add an index on the GROUP BY column.

Use ORDER BY NULL to suppress unnecessary sorting.

Force in‑memory processing with SQL_BIG_RESULT when possible.

12. Hardware / Network Bottlenecks

Insufficient CPU, memory, or I/O on the database server, as well as high network latency, can exacerbate all of the above issues. Align test‑environment settings with production (e.g., enable/disable index‑merge) to avoid surprising performance regressions.