9 Proven MySQL Query Optimizations to Slash Execution Time

1. LIMIT Clause

Pagination is a frequent source of performance problems. A naïve query like the following forces the engine to scan from the beginning even when the offset is large:

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

When the offset reaches a million rows, the database must count a million rows before returning the next ten, which is slow. Re‑writing the query to use the maximum value of the previous page eliminates the costly 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

MySQL silently converts mismatched types, which can break index usage. For example:

mysql> EXPLAIN EXTENDED SELECT *
      FROM my_balance b
      WHERE b.bpn = 14000000123
        AND b.isverified IS NULL;
mysql> SHOW WARNINGS;
| Warning | 1739 | Cannot use ref access on index 'bpn' due to type or collation conversion on field 'bpn' |

Here bpn is defined as VARCHAR(20); the numeric literal forces a conversion that disables the index.

3. Update/Delete with Sub‑queries

MySQL 5.6 introduced materialized sub‑queries for SELECT, but UPDATE/DELETE still execute the sub‑query as a dependent sub‑query, which is extremely slow. The original statement:

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
);

produces a DEPENDENT SUBQUERY plan. Re‑writing it as a JOIN changes the plan to DERIVED and reduces execution time 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 o.status = 'applying';

4. Mixed Sorting

MySQL cannot use an index for mixed ASC/DESC sorting, leading to full‑table scans. The original query:

SELECT *
FROM my_order o
INNER JOIN my_appraise a ON a.orderid = o.id
ORDER BY a.is_reply ASC, a.appraise_time DESC
LIMIT 0, 20;

can be split into two queries, each handling a single is_reply value, then UNION ALL the results:

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, appraisetime DESC
LIMIT 20;

This reduces execution time from 1.58 s to 2 ms.

5. EXISTS Clause

MySQL executes EXISTS as a nested sub‑query. The following statement suffers from this:

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;

Replacing EXISTS with an inner join removes the dependent 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

Conditions cannot be pushed into certain sub‑queries such as aggregates, LIMIT, UNION, or scalar sub‑queries. Example:

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

After moving the predicate before the aggregation, the plan becomes a simple index 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 to the leftmost table, sorting can be performed before the joins. Original query:

SELECT *
FROM my_order o
LEFT JOIN my_userinfo u ON o.uid = u.uid
LEFT JOIN my_productinfo p ON o.pid = p.pid
WHERE o.display = 0 AND o.ostaus = 1
ORDER BY o.selltime DESC
LIMIT 0, 15;

Re‑write to fetch the limited rows first, then join:

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;

The execution time drops from ~12 s to ~1 ms.

8. Intermediate Result Push‑down

In a query that joins a filtered sub‑query a with an aggregated sub‑query c, the aggregation scans the whole table. Original version:

SELECT a.*, c.allocated
FROM (
  SELECT resourceid
  FROM my_distribute d
  WHERE isdelete = 0 AND cusmanagercode = '1234567'
  ORDER BY salecode LIMIT 20
) a
LEFT JOIN (
  SELECT resourcesid, SUM(IFNULL(allocation,0) * 12345) allocated
  FROM my_resources
  GROUP BY resourcesid
) c ON a.resourceid = c.resourcesid;

By pushing the join condition into the aggregation, the scan is limited to matching rows, reducing runtime from 2 s to 2 ms.

9. Using WITH for Reuse

When a sub‑query is needed multiple times, a CTE makes the statement clearer and avoids repeated work:

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

These patterns illustrate how understanding MySQL's optimizer and applying algorithmic thinking can turn slow, resource‑hungry SQL into concise, high‑performance statements.