Common MySQL Query Performance Issues and Optimization Techniques

Pagination queries are a frequent source of performance problems. A simple query that filters by type , name and orders by create_time can benefit from a composite index on those columns, but using a large offset in LIMIT (e.g., LIMIT 1000000,10 ) forces the database to scan many rows before returning the requested ten.

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

To avoid the costly offset, the previous page's maximum create_time can be used as a condition, making the query time essentially constant regardless of table size.

SELECT *
FROM operation
WHERE type = 'SQLStats'
  AND name = 'SlowLog'
  AND create_time > '2017-03-16 14:00:00'
ORDER BY create_time
LIMIT 10;

Another common mistake is implicit type conversion. When a column defined as VARCHAR(20) is compared with a numeric literal, MySQL converts the column to a number, causing the index on the column to become unusable.

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

Updates or deletes that rely on subqueries often generate a DEPENDENT SUBQUERY execution plan, which is slow. Rewriting them as JOIN statements turns the plan into DERIVED and can reduce 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';

MySQL cannot use an index for mixed ordering (e.g., ordering by a boolean column then a datetime). By separating the two boolean values with UNION ALL and ordering each subset individually, the query can avoid a full table scan and achieve millisecond‑level performance.

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

The EXISTS clause often results in a nested subquery plan. Converting it to a JOIN eliminates the subquery and can cut execution time from seconds to a single 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;

Condition push‑down is not possible for certain constructs such as aggregation subqueries, subqueries with LIMIT , UNION / UNION ALL , or subqueries in the SELECT list. When the condition can be pushed down, rewriting the query to apply the filter before aggregation yields a much cheaper plan.

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

When the final ORDER BY and LIMIT operate on the leftmost table, the ordering and limiting can be performed first, then the remaining joins are applied. This early‑range reduction can shrink execution time from many seconds to around one millisecond.

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;

For queries that involve a small subquery used multiple times, a Common Table Expression ( WITH ) can materialize the subquery once and reuse it, dramatically reducing execution time.

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;

In summary, the database optimizer generates execution plans that are not always optimal. Understanding its behavior and applying techniques such as proper indexing, avoiding large offsets, rewriting subqueries as joins, pushing conditions down, and using CTEs can help write high‑performance SQL statements.