8 MySQL Query Optimizations to Slash Execution Time

1. LIMIT Clause

Pagination is a common scenario that often causes performance problems. A simple query with LIMIT 1000, 10 can be slow because MySQL must scan from the beginning to locate the 1,000,001‑st row, even if indexes exist.

SELECT *
FROM   operation
WHERE  type = 'SQLStats'
  AND  name = 'SlowLog'
ORDER BY create_time
LIMIT 1000, 10;

Rewriting the query to use the previous page's maximum value as a condition eliminates the need for a large offset:

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

When a column type does not match the literal type, MySQL converts the literal, which can invalidate indexes. Example: a VARCHAR(20) column compared with a numeric literal triggers a conversion warning and forces a full scan.

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

3. Update/Delete with Join

MySQL 5.6’s materialized subquery optimization applies only to SELECT. For UPDATE/DELETE, rewrite the statement as a JOIN to avoid dependent subqueries.

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

Rewritten as a JOIN, the execution plan changes from DEPENDENT SUBQUERY to DERIVED, reducing runtime from seconds to milliseconds:

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

4. Mixed Ordering

MySQL cannot use an index for mixed ORDER BY clauses, but splitting the query by the ordered column can improve performance.

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
ORDER BY is_reply ASC, appraise_time DESC
LIMIT 20;

5. EXISTS Clause

MySQL treats EXISTS as a nested subquery, which can be slow. Converting it to a JOIN removes the dependent subquery and speeds up execution dramatically.

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

Rewritten version:

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;

6. Condition Push‑Down

Conditions cannot be pushed down into complex views or subqueries such as aggregation, LIMIT‑containing subqueries, UNIONs, or subqueries in the SELECT list. Example of a query where the condition is applied after aggregation:

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

After confirming the condition can be applied before aggregation, rewrite it as:

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

7. Early Range Reduction

When the final WHERE clause and ORDER BY target the leftmost table, sort and limit that table first, then join the rest. 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. Pushing Intermediate Result Sets

Rewrite queries so that intermediate result sets are materialized and joined early, avoiding costly full‑table aggregations. Example using a CTE (WITH) to factor out a reusable subquery:

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, a
  WHERE  r.resourcesid = a.resourcesid
  GROUP BY resourcesid
) c ON a.resourceid = c.resourcesid;

These techniques illustrate how understanding the MySQL optimizer and rewriting SQL can turn multi‑second queries into millisecond‑level operations.

http://mysql.taobao.org/monthly/2016/07/08