8 Proven MySQL Tricks to Supercharge Query Performance

1. LIMIT clause

Pagination is a common scenario but often a performance pitfall. A simple query with LIMIT 1000,10 can be slow because the database must scan from the beginning to locate the millionth row, even with indexes.

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

Instead, 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;

This redesign makes query time essentially constant regardless of data volume.

2. Implicit conversion

Mismatched types between query variables and column definitions cause hidden performance costs. For example, comparing a VARCHAR column to a numeric literal forces MySQL to convert the column values, invalidating indexes.

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

The warning shows that the index on bpn cannot be used due to type conversion.

3. Update/Delete with joins

MySQL 5.6 introduced materialized subqueries for SELECTs, but UPDATE/DELETE still execute as dependent subqueries. Rewriting them as JOINs changes the execution plan from DEPENDENT SUBQUERY to DERIVED, cutting execution 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
);

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 indexes for mixed ORDER BY clauses, but when a column has only two distinct values (e.g., is_reply), you can split the query into two UNIONed parts, each sorting only one subset, reducing execution from 1.58 s to 2 ms.

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

5. EXISTS clause

MySQL still treats EXISTS as a nested subquery. Rewriting it as a JOIN eliminates the dependent subquery and drops execution time from 1.93 s to 1 ms.

SELECT *
FROM my_neighbor n
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. Predicate push‑down

External predicates cannot be pushed into complex views or subqueries such as aggregate subqueries, LIMIT‑containing subqueries, UNION/UNION ALL subqueries, or subqueries in SELECT lists. Recognizing when push‑down is possible allows rewriting like:

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

7. Early range reduction

When the final WHERE clause and ORDER BY apply to the leftmost table, sort that table first and then join. This reduces a query that originally took 12 s to 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. Pushing intermediate result sets

When a subquery produces a large intermediate result, rewrite it to filter early or use a CTE ( WITH) to avoid repeated evaluation. This can cut execution from seconds to 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) allocated
  FROM my_resources r
  JOIN a ON r.resourcesid = a.resourceid
  GROUP BY resourcesid
) c ON a.resourceid = c.resourcesid;

Database compilers generate execution plans that dictate how SQL runs, but they are not perfect. Understanding their behavior and applying these patterns helps write high‑performance SQL.

Source: juejin.cn/post/6844903949359808526