MySQL Query Optimization Techniques and Common Pitfalls

The author, a senior architect, explains several typical performance problems in MySQL and provides concrete rewrites that dramatically reduce execution time, often from seconds to milliseconds.

1. LIMIT clause – Using large offsets (e.g., LIMIT 1000000,10 ) forces the engine to scan many rows. Rewriting the query to filter by the last retrieved value (e.g., create_time > '2017-03-16 14:00:00' ) makes the cost independent of table size.

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

2. Implicit conversion – Comparing a numeric column with a string (e.g., bpn = 14000000123 where bpn is VARCHAR(20) ) forces MySQL to convert the column, disabling index usage. Ensure matching data types or cast explicitly.

SELECT *
FROM my_balance b
WHERE b.bpn = CAST(14000000123 AS CHAR)
  AND b.isverified IS NULL;

3. Update/Delete with sub‑queries – MySQL 5.6 executes dependent sub‑queries inefficiently. Converting them to JOINs changes the execution plan from DEPENDENT SUBQUERY to DERIVED , cutting runtime from seconds to milliseconds.

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

4. Mixed sorting – MySQL cannot use indexes for mixed ORDER BY columns. By separating the result set with UNION ALL for each is_reply value and ordering only by the timestamp, execution time drops from seconds to a few milliseconds.

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

5. EXISTS clause – EXISTS is implemented as a nested sub‑query. Replacing it with an explicit JOIN removes the sub‑query and reduces execution time from seconds to milliseconds.

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

6. Condition push‑down – Conditions cannot be pushed into complex sub‑queries such as aggregated or UNION queries. By moving the filter into the outer query after materializing the sub‑query, the plan becomes simpler and faster.

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

7. Early range reduction – When the main table’s WHERE clause and ORDER BY apply to the leftmost table, sort and limit can be performed before the joins, dramatically shrinking the intermediate result set.

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 15;

8. Intermediate result push‑down – For a LEFT JOIN where the right side aggregates the whole table, filter the right side by the keys needed for the join, or use a CTE to avoid repeated sub‑query evaluation.

WITH a AS (
  SELECT resourceid
  FROM my_distribute d
  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;

The article concludes that understanding the database optimizer’s behavior and applying algorithmic thinking when writing SQL leads to more efficient, maintainable queries.