MySQL Query Optimization Techniques and Common Pitfalls

The execution order of an SQL statement in MySQL follows FROM → JOIN → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT. Understanding this order helps identify performance bottlenecks.

1. LIMIT Clause

Pagination using LIMIT offset, count can become slow when the offset is large because MySQL must scan rows from the beginning. A better approach is to use the maximum value of the previous page as a filter, e.g.:

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

This design keeps query time constant regardless of table size.

2. Implicit Conversion

When a column type does not match the literal type, MySQL implicitly converts the column, which can disable index usage. Example:

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

Because bpn is VARCHAR(20), MySQL converts the string to a number, causing the index on bpn to be ignored.

3. Join‑Based Update/Delete

MySQL 5.6 supports materialized sub‑queries for SELECT, but UPDATE/DELETE still execute as dependent sub‑queries. Rewriting them as JOINs can change the execution plan from DEPENDENT SUBQUERY to DERIVED, reducing execution time 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 an index for mixed ASC/DESC sorting. By separating the query into two UNION ALL parts, each with a single sort direction, the planner can use index scans and reduce execution time dramatically.

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

5. EXISTS Removal

Using EXISTS often forces a nested sub‑query. Replacing it with an INNER JOIN can eliminate the dependent sub‑query and cut execution time from seconds to a millisecond.

SELECT *
FROM my_neighbor n
INNER JOIN message_info m ON n.id = m.neighbor_id
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
  AND m.inuser = 'xxx';

6. Condition Push‑Down

External conditions cannot be pushed into complex views, sub‑queries with LIMIT, UNION, or aggregation. When possible, push the condition directly into the base table to let the optimizer use indexes.

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

7. Early Range Reduction

When the WHERE clause and ORDER BY apply to the leftmost table, filter and sort that table first, then join the remaining tables. This reduces the amount of data processed in later joins.

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;

8. Intermediate Result Push‑Down

When a sub‑query produces a large intermediate result set, restrict it early by joining only the needed keys. Using a WITH clause (CTE) can make the query clearer and 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 r.resourcesid,
         SUM(IFNULL(r.allocation,0) * 12345) AS allocated
  FROM my_resources r
  JOIN a ON r.resourcesid = a.resourceid
  GROUP BY r.resourcesid
) c ON a.resourceid = c.resourcesid;

Understanding how the database compiler generates execution plans and applying these rewrite techniques enables developers to write high‑performance SQL.

In summary, be aware of execution order, avoid large OFFSETs, prevent implicit conversions that break indexes, rewrite sub‑queries as joins, push conditions down to base tables, and use CTEs for clarity and efficiency.