8 MySQL Performance Tricks to Slash Query Time from Seconds to Milliseconds

1. LIMIT Clause Optimization

Pagination using LIMIT offset, count forces MySQL to scan rows from the beginning up to offset. When offset is large (e.g., LIMIT 1000000,10), the query becomes slow even though only a few rows are returned because the engine cannot jump directly to the required position.

A common remedy is to use the maximum key value from the previous page as a filter instead of an offset. For example, if the primary key is id:

SELECT * FROM t
WHERE id > :last_id
ORDER BY id ASC
LIMIT 10;

This eliminates the offset scan; the execution time stays roughly constant regardless of table size.

2. Implicit Type Conversion

When a column’s data type does not match the type of a literal or parameter, MySQL performs an implicit conversion. For a VARCHAR(20) column bpn compared with a numeric literal, MySQL converts the string to a number before comparison, causing the index on bpn to be ignored.

SELECT * FROM orders
WHERE bpn = 12345;  -- bpn is VARCHAR, 12345 is numeric

To preserve index usage, ensure the literal matches the column type (e.g., quote the value) or cast explicitly.

3. Update/Delete with JOIN

MySQL 5.6 introduced materialized subquery (derived table) optimization for SELECTs, but UPDATE/DELETE statements still execute dependent subqueries, which can be extremely slow.

Original UPDATE pattern (dependent subquery):

UPDATE t1
SET col = (SELECT val FROM t2 WHERE t2.id = t1.ref_id);

Execution plan shows DEPENDENT SUBQUERY and may take seconds.

Rewrite using JOIN so the optimizer can treat it as a derived table:

UPDATE t1
JOIN t2 ON t2.id = t1.ref_id
SET t1.col = t2.val;

After rewriting, the plan changes to DERIVED and execution time drops from seconds to milliseconds.

4. Mixed Sorting

MySQL cannot use an index when the ORDER BY clause mixes indexed and non‑indexed columns. In a query that orders by create_time DESC, is_reply (where is_reply is a tinyint with only 0/1 values), the optimizer falls back to a full table scan.

Original query:

SELECT * FROM comments
WHERE post_id = :pid
ORDER BY create_time DESC, is_reply;

Execution plan shows a full scan and takes >1 s on large tables.

Because is_reply has only two distinct values, we can split the query or add a composite index that matches the ordering, reducing the runtime to a few milliseconds.

5. EXISTS vs JOIN

MySQL treats EXISTS subqueries as nested loops, which can be costly. Replacing EXISTS with an equivalent JOIN often allows the optimizer to use hash or merge joins.

Example with EXISTS:

SELECT a.* FROM a
WHERE EXISTS (SELECT 1 FROM b WHERE b.a_id = a.id AND b.status = 'active');

Plan shows a dependent subquery and execution time ~1.9 s.

Rewrite using JOIN:

SELECT DISTINCT a.* FROM a
JOIN b ON b.a_id = a.id
WHERE b.status = 'active';

After rewriting, the plan no longer contains a subquery and execution drops to ~1 ms.

6. Condition Pushdown Limitations

Some query constructs prevent the outer WHERE clause from being pushed down into subqueries or views, causing extra materialization. Typical blockers are:

Aggregating subqueries (e.g., GROUP BY inside a subquery)

Subqueries that contain

LIMIT

UNION

or UNION ALL subqueries

Scalar subqueries used in the SELECT list

Example of a blocked pushdown:

SELECT * FROM (
  SELECT user_id, COUNT(*) AS cnt FROM orders GROUP BY user_id
) AS agg
WHERE cnt > 10;

The optimizer applies the WHERE cnt > 10 after the aggregation, leading to a full scan of the derived table.

After confirming that the predicate can be applied earlier, rewrite the query to push the condition into the subquery:

SELECT * FROM (
  SELECT user_id, COUNT(*) AS cnt FROM orders GROUP BY user_id HAVING cnt > 10
) AS agg;

The plan now shows the filter applied during aggregation, reducing rows processed.

7. Early Data Reduction (Pre‑Sorting & LIMIT)

When the final WHERE clause and ORDER BY refer only to the leftmost table in a LEFT JOIN, the query can be reordered to sort and limit that table first, dramatically shrinking the intermediate row set.

Original query (90 万 rows, ~12 s):

SELECT o.id, o.name, d.detail
FROM my_order o
LEFT JOIN order_detail d ON d.order_id = o.id
WHERE o.status = 'finished'
ORDER BY o.create_time DESC
LIMIT 10;

By sorting my_order first and applying LIMIT 10 before the join, the derived table contains only ten rows, and the join finishes in ~1 ms.

SELECT o.id, o.name, d.detail
FROM (
  SELECT * FROM my_order
  WHERE status = 'finished'
  ORDER BY create_time DESC
  LIMIT 10
) AS o
LEFT JOIN order_detail d ON d.order_id = o.id;

8. Pushing Down Intermediate Result Sets (CTE)

Repeated subqueries increase parsing and execution overhead. Using a Common Table Expression (CTE) with WITH isolates the subquery once and lets the optimizer reuse the materialized result.

Problematic query (subquery c aggregates the whole table, subquery a appears multiple times):

SELECT r.resourceid, a.val, b.val, c.total
FROM resource r
LEFT JOIN (SELECT ... ) a ON a.id = r.id
LEFT JOIN (SELECT ... ) b ON b.id = r.id
LEFT JOIN (SELECT resourceid, SUM(amount) AS total FROM transactions GROUP BY resourceid) c
  ON c.resourceid = r.resourceid;

Rewrite with CTE to compute c once and reference it directly:

WITH c AS (
  SELECT resourceid, SUM(amount) AS total
  FROM transactions
  GROUP BY resourceid
)
SELECT r.resourceid, a.val, b.val, c.total
FROM resource r
LEFT JOIN (SELECT ... ) a ON a.id = r.id
LEFT JOIN (SELECT ... ) b ON b.id = r.id
LEFT JOIN c ON c.resourceid = r.resourceid;

Execution time drops from seconds to a few milliseconds.

Summary

MySQL’s optimizer generates execution plans based on query structure. Understanding its behavior—how offsets, implicit conversions, subquery materialization, and condition pushdown affect plan choice—allows developers to rewrite SQL for predictable, high‑performance execution. Common techniques include:

Replace large OFFSET with a “greater‑than last key” filter.

Match literal types to column definitions to keep indexes usable.

Rewrite UPDATE/DELETE statements as JOINs to avoid dependent subqueries.

Design indexes that cover the full ORDER BY clause or split sorting logic.

Prefer JOINs over EXISTS when possible.

Identify and eliminate constructs that block condition pushdown.

Apply early sorting and LIMIT before joins to shrink intermediate result sets.

Use CTEs ( WITH) to compute reusable subqueries once.

Applying these patterns consistently yields queries whose execution time remains stable as data volume grows.