Common MySQL Query Performance Pitfalls and Their Optimizations

Pagination is a typical scenario where MySQL performance degrades, especially when using a large offset in a LIMIT clause (e.g., LIMIT 1000000,10). The optimizer must scan rows from the beginning, ignoring indexes. Rewriting the query to filter by the last seen create_time value eliminates the offset and keeps execution time constant.

SELECT * FROM operation
WHERE type = 'SQLStats' AND name = 'SlowLog' AND create_time > '2017-03-16 14:00:00'
ORDER BY create_time
LIMIT 10;

Implicit type conversion can also break indexes. When a numeric literal is compared to a VARCHAR column, MySQL converts the column to a number, causing the index on the column to be ignored.

SELECT * FROM my_balance b
WHERE b.bpn = 14000000123 AND b.isverified IS NULL;

Updates or deletes that use sub‑queries (DEPENDENT SUBQUERY) are slow. Converting them to a JOIN changes the execution plan to DERIVED and can reduce runtime from seconds to milliseconds.

UPDATE operation o
JOIN (SELECT id FROM operation WHERE `group` = 123 AND status NOT IN ('done') ORDER BY parent, id LIMIT 1) t
ON o.id = t.id
SET status = 'applying';

MySQL cannot use indexes for mixed ordering (e.g., ordering by a boolean column then a timestamp). Splitting the query into two UNION ALL parts—one for each boolean value—allows each part to use an index and reduces execution time dramatically.

SELECT * FROM (
  SELECT * FROM my_order o JOIN my_appraise a ON a.orderid = o.id AND a.is_reply = 0 ORDER BY a.appraise_time DESC LIMIT 0,20
  UNION ALL
  SELECT * FROM my_order o JOIN my_appraise a ON a.orderid = o.id AND a.is_reply = 1 ORDER BY a.appraise_time DESC LIMIT 0,20
) t
ORDER BY is_reply ASC, appraise_time DESC
LIMIT 20;

Replacing an EXISTS sub‑query with a direct JOIN removes the dependent sub‑query and can cut execution time from seconds to a millisecond.

SELECT *
FROM my_neighbor n
INNER JOIN message_info m ON n.id = m.neighbor_id AND m.inuser = 'xxx'
LEFT JOIN my_neighbor_apply sra ON n.id = sra.neighbor_id AND sra.user_id = 'xxx'
WHERE n.topic_status < 4 AND n.topic_type <> 5;

Condition push‑down fails for certain constructs such as aggregated sub‑queries, LIMIT‑bearing sub‑queries, UNION/UNION ALL sub‑queries, and scalar sub‑queries in the SELECT list. By moving the filter into the inner query, the plan changes from DERIVED to a simple index lookup.

SELECT target, COUNT(*)
FROM operation
WHERE target = 'rm-xxxx'
GROUP BY target;

When a large result set is sorted after many LEFT JOINs, applying ORDER BY and LIMIT to the primary table first (derived table) can shrink the intermediate data dramatically.

SELECT *
FROM (
  SELECT * FROM my_order o
  WHERE o.display = 0 AND o.ostaus = 1
  ORDER BY o.selltime DESC
  LIMIT 0,15
) o
LEFT JOIN my_userinfo u ON o.uid = u.uid
LEFT JOIN my_productinfo p ON o.pid = p.pid
ORDER BY o.selltime DESC
LIMIT 0,15;

For queries that join a large aggregated sub‑query, pushing the join condition into the aggregation (or using a WITH clause) limits the amount of data processed, turning a multi‑second execution into a few milliseconds.

WITH a AS (
  SELECT resourceid FROM my_distribute
  WHERE isdelete = 0 AND cusmanagercode = '1234567'
  ORDER BY salecode LIMIT 20
)
SELECT a.*, c.allocated
FROM a
LEFT JOIN (
  SELECT resourcesid, SUM(IFNULL(allocation,0)*12345) AS allocated
  FROM my_resources r
  JOIN a ON r.resourcesid = a.resourceid
  GROUP BY resourcesid
) c ON a.resourceid = c.resourcesid;

In summary, understanding MySQL's query optimizer and rewriting queries to avoid offset scans, implicit conversions, dependent sub‑queries, and unnecessary full‑table aggregations can lead to orders‑of‑magnitude performance improvements.