7 Proven MySQL Query Optimizations to Slash Execution Time

1. LIMIT Clause

Pagination is a frequent scenario but often problematic; using a large offset like LIMIT 1000000,10 forces the database to scan from the beginning. Rewriting the query to use the previous page's maximum value (e.g., create_time > '2017-03-16 14:00:00') keeps execution time constant regardless of data 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

When a query compares a VARCHAR column with a numeric literal, MySQL converts the string to a number, causing index loss. For example, comparing bpn (VARCHAR) with 14000000123 triggers a full scan.

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

3. Join Updates and Deletes

MySQL 5.6’s materialized view only optimizes SELECTs; UPDATE/DELETE statements with sub‑queries should be rewritten as JOINs. The original sub‑query leads to a DEPENDENT SUBQUERY plan (seconds), while the JOIN version becomes a DERIVED plan (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 o.status = 'applying';

4. Mixed Sorting

MySQL cannot use an index for mixed ASC/DESC sorting. By separating the two is_reply values with UNION ALL, each sub‑query can use a simple index, reducing execution from 1.58 s to 2 ms.

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 Clause

MySQL treats EXISTS as a nested sub‑query, which is slow. Rewriting it as a JOIN eliminates the sub‑query and drops execution time from 1.93 s to 1 ms.

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

Aggregated sub‑queries

Sub‑queries with LIMIT

UNION / UNION ALL sub‑queries

Sub‑queries in output fields

Example: moving the WHERE target='rm-xxxx' condition into the derived table changes the plan from a derived scan to a simple indexed lookup.

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

7. Early Range Reduction

When the final WHERE and ORDER BY apply only to the leftmost table, sort and limit that table first, then join the others. This reduces the original 12 s plan to about 1 ms.

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

For a large aggregation sub‑query, limit the result set before joining. Using a WITH clause removes duplicated sub‑queries and improves performance from seconds to milliseconds.

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;

Summary

The database compiler generates execution plans that dictate how SQL runs, but it is not perfect. Understanding its behavior—especially around LIMIT offsets, type conversion, sub‑query materialization, and condition push‑down—allows developers to write high‑performance SQL. Using WITH for complex queries keeps them clear and reduces load on the database.