8 MySQL Optimizations to Slash Query Times from Seconds to Milliseconds

1. LIMIT clause

Pagination is a common scenario that often causes performance problems. A simple query like the one below benefits from a composite index on type, name, and create_time, allowing the optimizer to use the index for sorting and dramatically improving speed.

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

When the LIMIT clause becomes LIMIT 1000000,10, the query remains slow because the database must scan from the beginning to locate the millionth row, even with an index. A better approach is to use the maximum value from the previous page as a condition, rewriting the query as:

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

With this design the execution time stays roughly constant regardless of data volume.

2. Implicit conversion

Mismatched types between query literals and column definitions cause implicit conversion, which can invalidate indexes. For example, comparing a VARCHAR(20) column bpn with a numeric literal forces MySQL to convert the column to a number, preventing index usage.

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

The warning shows that MySQL cannot use the index on bpn because of the type conversion.

3. Update/Delete with JOIN

MySQL 5.6 introduced materialized subquery optimization for SELECTs, but UPDATE and DELETE statements still execute dependent subqueries, leading to poor performance. Rewriting such statements as JOINs changes the execution plan from DEPENDENT SUBQUERY to DERIVED, reducing execution time from seconds to milliseconds.

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 version:

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 ordering (different ASC/DESC directions). By splitting the query into two subqueries—one for each is_reply value—and then UNION ALL them, the execution time drops from 1.58 seconds to 2 milliseconds.

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

5. EXISTS clause

MySQL still executes EXISTS as a nested subquery. Replacing it with a JOIN eliminates the subquery and reduces execution time from 1.93 seconds to 1 millisecond.

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

External query conditions cannot be pushed down into complex views or subqueries when they involve aggregation, LIMIT, UNION, or subqueries in the SELECT list. By moving the condition into the outer query, the plan changes from a derived table scan to a simple indexed lookup.

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

7. Early range reduction

When the final WHERE clause and ORDER BY target the leftmost table, sorting can be performed before the joins. Reordering the query to first filter and sort my_order reduces execution time from ~12 seconds to ~1 millisecond.

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

In a query that joins a large aggregated subquery c with a filtered set a, pushing the join condition into the subquery and using a WITH clause eliminates unnecessary full‑table aggregation, cutting execution time from 2 seconds to 2 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, a
  WHERE r.resourcesid = a.resourcesid
  GROUP BY resourcesid
) c ON a.resourceid = c.resourcesid;

Understanding how the MySQL optimizer generates execution plans and the limitations of its compiler enables developers to write high‑performance SQL by applying these techniques.