MySQL Query Optimization Techniques: LIMIT, Implicit Conversion, Joins and More

The article presents a series of common performance problems in MySQL queries and demonstrates how to rewrite them for better execution plans.

1. LIMIT clause

Using a large offset such as LIMIT 1000000,10 forces MySQL to scan many rows even with indexes. A better approach is to use the maximum value of the previous page as a filter.

Original query:

SELECT *
FROM   operation
WHEREtype = 'SQLStats'
      ANDname = 'SlowLog'
ORDERBY create_time
LIMIT1000, 10;

Rewritten query:

SELECT   *
FROM     operation
WHEREtype = 'SQLStats'
ANDname = 'SlowLog'
AND      create_time > '2017-03-16 14:00:00'
ORDERBY create_time limit10;

The execution time becomes almost constant regardless of data volume.

2. Implicit conversion

When a column type does not match the literal type, MySQL converts the literal, causing index loss. 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' |

Developers should ensure type consistency to keep indexes usable.

3. Join update / delete

MySQL 5.6 executes sub‑queries in UPDATE/DELETE as dependent subqueries, which are slow. Rewriting them as JOINs dramatically improves performance.

Original UPDATE:

UPDATE operation o
SETstatus = 'applying'
WHERE  o.id IN (SELECTid
               FROM   (SELECT o.id,
                        o.status
                        FROM   operation o
                        WHERE  o.group = 123
                               AND o.status NOTIN ('done')
                        ORDERBY o.parent,
                                 o.id
                        LIMIT1) t);

Rewritten UPDATE:

UPDATE operation o
      JOIN  (SELECT o.id,
                     o.status
              FROM   operation o
              WHERE  o.group = 123
                     AND o.status NOTIN ('done')
              ORDERBY o.parent,
                       o.id
              LIMIT1) t
        ON o.id = t.id
SETstatus = 'applying'

The plan changes from DEPENDENT SUBQUERY to DERIVED, reducing execution time from seconds to milliseconds.

4. Mixed sorting

MySQL cannot use indexes for mixed ORDER BY columns. Splitting the query by the binary column and using UNION ALL can avoid filesort.

Original query:

SELECT *
FROM   my_order o
      INNERJOIN my_appraise a ON a.orderid = o.id
ORDERBY a.is_reply ASC,
          a.appraise_time DESC
LIMIT0, 20

Rewritten query (using two sub‑queries and UNION ALL):

SELECT *
FROM   ((SELECT *
        FROM   my_order o
        INNERJOIN my_appraise a
          ON a.orderid = o.id
         AND is_reply = 0
        ORDERBY appraise_time DESC
        LIMIT0, 20)
       UNIONALL
       (SELECT *
        FROM   my_order o
        INNERJOIN my_appraise a
          ON a.orderid = o.id
         AND is_reply = 1
        ORDERBY appraise_time DESC
        LIMIT0, 20)) t
ORDERBY  is_reply ASC,
         appraisetime DESC
LIMIT20;

Execution time drops from 1.58 s to 2 ms.

5. EXISTS clause

Replacing EXISTS with an equivalent JOIN removes the dependent subquery.

Original query:

SELECT *
FROM   my_neighbor n
      LEFTJOIN my_neighbor_apply sra
          ON n.id = sra.neighbor_id
             AND sra.user_id = 'xxx'
WHERE  n.topic_status < 4
      ANDEXISTS(SELECT1
                FROM   message_info m
                WHERE  n.id = m.neighbor_id
                       AND m.inuser = 'xxx')
      AND n.topic_type <> 5

Rewritten query:

SELECT *
FROM   my_neighbor n
      INNERJOIN message_info m
          ON n.id = m.neighbor_id
             AND m.inuser = 'xxx'
      LEFTJOIN 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

The plan changes from DEPENDENT SUBQUERY to SIMPLE joins, cutting execution time from ~2 s to 1 ms.

6. Predicate pushdown

Conditions cannot be pushed into subqueries that involve aggregation, LIMIT, UNION, or appear in the SELECT list. Moving the predicate before the aggregation often yields a simpler plan.

Original query:

SELECT *
FROM   (SELECT target,
                Count(*)
        FROM   operation
        GROUPBY target) t
WHERE  target = 'rm-xxxx'

Rewritten query:

SELECT target,
       Count(*)
FROM   operation
WHERE  target = 'rm-xxxx'
GROUPBY target

The plan becomes a simple index lookup.

7. Early limit (pre‑filtering)

When the final ORDER BY and WHERE apply to the leftmost table, apply LIMIT before joining other tables.

Original query:

SELECT *
FROM   my_order o
      LEFTJOIN my_userinfo u ON o.uid = u.uid
      LEFTJOIN my_productinfo p ON o.pid = p.pid
WHERE  ( o.display = 0 )
      AND ( o.ostaus = 1 )
ORDERBY o.selltime DESC
LIMIT0, 15

Rewritten query:

SELECT *
FROM (
SELECT *
FROM   my_order o
WHERE  ( o.display = 0 )
      AND ( o.ostaus = 1 )
ORDERBY o.selltime DESC
LIMIT0, 15) o
     LEFTJOIN my_userinfo u ON o.uid = u.uid
     LEFTJOIN my_productinfo p ON o.pid = p.pid
ORDERBY  o.selltime DESC
limit0, 15

Execution time shrinks to about 1 ms.

8. Intermediate result pushdown with CTE

Repeated subqueries can be factored out using WITH (CTE) to avoid redundant scans.

Original approach uses the same subquery a several times. Using a CTE:

WITH a AS (
        SELECT   resourceid
        FROM     my_distribute d
        WHERE    isdelete = 0
        AND      cusmanagercode = '1234567'
        ORDERBY   salecode limit20)
SELECT    a.*,
          c.allocated
FROM      a
LEFTJOIN (SELECT   resourcesid,
                 sum(ifnull(allocation, 0) * 12345) allocated
          FROM     my_resources r,
                   a
          WHERE    r.resourcesid = a.resourcesid
          GROUPBY  resourcesid) c
ON        a.resourceid = c.resourcesid

This reduces execution time from seconds to a few milliseconds.

Conclusion

The MySQL optimizer decides the actual execution strategy, but it is not perfect. Understanding its behavior and rewriting queries—using proper indexes, avoiding large offsets, converting subqueries to joins, applying early limits, and leveraging CTEs—helps produce high‑performance SQL.