How to Supercharge MySQL Queries: 8 Proven Optimization Techniques

1. LIMIT clause

Pagination is a frequent use case but can cause severe performance issues when using large offsets. A simple query with LIMIT 1000, 10 forces the database to scan many rows before returning ten.

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

Rewriting the query to use the maximum value from the previous page 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;

The execution time becomes essentially constant regardless of data size.

2. Implicit conversion

When a column type does not match the query value, MySQL may implicitly convert data, causing index loss. For example, comparing a VARCHAR column to a numeric literal triggers a conversion that prevents index usage.

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

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

3. Update/Delete with subqueries

MySQL 5.6’s materialized subquery optimization applies only to SELECT statements. UPDATE or DELETE statements that contain subqueries should be rewritten as JOINs 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 ASC/DESC ordering. By separating the query into two parts—one for each is_reply value—and then UNION ALL, the execution time drops dramatically.

SELECT *
FROM (
  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
) t
ORDER BY is_reply ASC, appraisetime DESC
LIMIT 20;

5. EXISTS clause

Using EXISTS often leads to nested subqueries. Replacing it with a JOIN can eliminate the subquery and cut execution time from seconds to a millisecond.

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 through certain constructs such as aggregate subqueries, LIMIT subqueries, UNION/UNION ALL, or subqueries in the SELECT list. By moving the filter into the outer query, the plan becomes more efficient.

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

7. Early result narrowing

When the final WHERE clause and ORDER BY apply only to the leftmost table, fetch the limited rows first and then join the remaining tables. This reduces the amount of data processed in the 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. Intermediate result push‑down

For queries that join a derived table with a large aggregation, push the filter into the aggregation or rewrite using a CTE to avoid scanning the whole table.

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

Summary

The database optimizer generates execution plans that dictate how SQL runs, but it is not perfect. Understanding its behavior and applying techniques such as proper indexing, avoiding implicit conversions, rewriting subqueries as joins, pushing conditions down, and using CTEs can dramatically improve query performance across many database systems.