8 Proven MySQL Query Optimizations to Slash Execution Time

1. LIMIT Clause and Keyset Pagination

Traditional pagination using LIMIT offset, count becomes slow when the offset is large because the database must scan rows from the beginning. Instead, use the previous page's maximum value in a WHERE clause to filter directly:

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 keyset pagination keeps query time constant regardless of table size.

2. Implicit Type Conversion

Comparing a VARCHAR column with a numeric literal forces MySQL to convert the column to a number, causing the index on that column to be ignored. Example:

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

The warning shows that the index on bpn cannot be used because of the conversion.

3. Update/Delete with JOIN

MySQL 5.6 materializes subqueries only for SELECT statements. UPDATE statements that contain a subquery are executed as dependent subqueries, which are very slow. Rewrite the UPDATE using a JOIN:

UPDATE operation o
JOIN (
  SELECT o.id
  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';

The execution plan changes from DEPENDENT SUBQUERY to DERIVED, reducing execution time from seconds to a few milliseconds.

4. Mixed Ordering

MySQL cannot use an index when ordering by multiple columns with different sort directions. Split the query into two parts, each handling one value of is_reply, and combine them with UNION ALL:

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

This reduces execution time from 1.58 s to about 2 ms.

5. EXISTS Clause

MySQL treats EXISTS as a nested subquery, which can be slow. Rewriting the query as a JOIN eliminates the subquery and drops execution time from ~1.9 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. Predicate Push‑Down

Conditions cannot be pushed into certain subqueries such as aggregated subqueries, subqueries with LIMIT, or UNION queries. Example of a derived table where the filter is applied after aggregation:

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

After confirming the predicate can be applied before grouping, rewrite as:

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

The plan changes to a simple index lookup.

7. Early Data Reduction

When the final WHERE clause and ordering apply only to the leftmost table, fetch the limited rows first and then join the other tables. Original query scans ~900 k rows; the rewritten version limits the main table before the joins, cutting execution time to ~1 ms.

SELECT * FROM (
  SELECT *
  FROM my_order o
  WHERE o.display = 0 AND o.ostaus = 1
  ORDER BY o.selltime DESC
  LIMIT 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 Set Push‑Down with CTE

When a subquery aggregates the whole table, rewrite using a Common Table Expression (CTE) so that only the rows needed for the join are processed. This reduces a 2‑second query to about 2 ms.

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;

Conclusion

The database optimizer generates execution plans but is not perfect. Understanding its limitations and applying techniques such as keyset pagination, avoiding implicit conversions, rewriting UPDATE/DELETE with joins, splitting mixed‑order queries, replacing EXISTS with joins, pushing predicates, early data reduction, and using CTEs can dramatically improve SQL performance across different database systems.