How to Supercharge MySQL Queries: 8 Proven Optimization Techniques

1. LIMIT Clause

Pagination is a common scenario but often problematic. A typical query may benefit from a composite index on type, name, and create_time to make sorting efficient.

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

When the OFFSET is large (e.g., LIMIT 1000000,10), the query remains slow because the database must scan from the beginning. A better approach is to use the maximum value of the previous page as a condition:

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 rewrite the execution time stays constant regardless of data volume.

2. Implicit Conversion

Mismatched types between query variables and column definitions cause hidden conversions. 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' |

The column bpn is defined as VARCHAR(20); MySQL converts the string to a number, invalidating the index.

3. Join Update and Delete

MySQL 5.6 introduced materialization for SELECTs, but UPDATE/DELETE still need manual rewriting into JOINs. The original UPDATE uses a dependent subquery:

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

Its execution plan shows a DEPENDENT SUBQUERY. Rewriting with a JOIN changes the plan to DERIVED and speeds the query 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 Sorting

MySQL cannot use an index for mixed ORDER BY columns. The original query forces a full table scan:

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;

Because is_reply has only two values, we can split the query by that flag and UNION the results, reducing execution time from 1.58 s to 2 ms:

SELECT *
FROM ((SELECT *
       FROM my_order o
       INNER JOIN my_appraise a ON a.orderid = o.id
       AND 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
       AND is_reply = 1
       ORDER BY appraise_time DESC
       LIMIT 0, 20)) t
ORDER BY is_reply ASC, appraisetime DESC
LIMIT 20;

5. EXISTS Clause

MySQL still executes EXISTS as a nested subquery. The original statement:

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 eliminates the subquery and cuts the runtime 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 Pushdown

External query conditions cannot be pushed into complex views or subqueries in the following cases:

Aggregated subqueries

Subqueries containing

LIMIT

UNION

or UNION ALL subqueries

Subqueries appearing in output columns

Example where the condition is applied after aggregation:

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

After confirming the condition can be pushed down, rewrite as:

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

7. Early Range Reduction

Original query joins three tables and then sorts a large result set, taking over 12 s:

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;

Since the filter and ordering apply to the leftmost table my_order, we can sort and limit it first, then join the other tables. The rewritten query runs in 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 Pushdown

Original query with a full‑table aggregation subquery:

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;

Because the final result only needs rows that match resourceid, we push the condition into the aggregation subquery, reducing execution time from 2 s to 2 ms:

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 r,
       (SELECT resourceid
        FROM my_distribute d
        WHERE isdelete = 0
          AND cusmanagercode = '1234567'
        ORDER BY salecode
        LIMIT 20) a
  WHERE r.resourcesid = a.resourcesid
  GROUP BY resourcesid
) c
ON a.resourceid = c.resourcesid;

Using a WITH clause makes the query clearer and avoids repeated subqueries:

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;

Conclusion

The database optimizer generates execution plans that determine how SQL runs, but it is not perfect. Understanding optimizer behavior and applying techniques such as smarter LIMIT usage, avoiding implicit conversions, rewriting subqueries with JOINs, pushing conditions, early range reduction, and using WITH can dramatically improve performance.

Adopting algorithmic thinking when designing data models and writing SQL, and habitually using WITH for complex queries, leads to cleaner statements and lighter load on the database.