Bug #75233 Explain for LIMIT query shows filesort but execution is done with index only
Submitted: 16 Dec 2014 9:17 Modified: 13 Feb 2015 21:49
Reporter: Olav Sandstå Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.7.6 OS:Any
Assigned to: CPU Architecture:Any

[16 Dec 2014 9:17] Olav Sandstå
Description:
For the following query that contains an "ORDER BY LIMIT n", explain says the "order by" will be executed by doing file sort of the result, but the actual execution is done by switching to another index which produces results in the "order by" order and thus avoids doing the file sort:

FROM t1 JOIN t2 ON t1.i1=t2.i1
WHERE t2.i2 > 3
ORDER BY t1.i1 LIMIT 20;

Explain for this query:
=======================

id select_type  table type possible_keys key   key_len ref        rows  filtered Extra
1  SIMPLE       t1    ALL  k1            NULL  NULL    NULL       1000  100.00   Using filesort
1  SIMPLE       t2    ref  k1            k1    4       test.t1.i1 2     33.33    Using where

Statistics about use of sorting from running the query:
=======================================================

SHOW STATUS LIKE 'Sort%';
Variable_name   Value
Sort_merge_passes       0
Sort_range      0
Sort_rows       0
Sort_scan       0

which shows no file sort was done during execution.

How to repeat:
CREATE TABLE t0 (
  i0 INTEGER NOT NULL
);

INSERT INTO t0 VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);

CREATE TABLE t1 (
  pk INTEGER PRIMARY KEY,
  i1 INTEGER NOT NULL,
  i2 INTEGER NOT NULL,
  INDEX k1 (i1)
) ENGINE=InnoDB;

INSERT INTO t1
SELECT a0.i0 + 10*a1.i0 + 100*a2.i0,
       (a0.i0 + 10*a1.i0 + 100*a2.i0) % 50,
       a0.i0 + 10*a1.i0 + 100*a2.i0
FROM t0 AS a0, t0 AS a1, t0 AS a2;

CREATE TABLE t2 (
  pk INTEGER PRIMARY KEY,
  i1 INTEGER NOT NULL,
  i2 INTEGER NOT NULL,
  INDEX k1 (i1)
) ENGINE=InnoDB;

INSERT INTO t2
SELECT a0.i0 + 10*a1.i0 + 100*a2.i0,
       (a0.i0 + 10*a1.i0 + 100*a2.i0) % 500,
       a0.i0 + 10*a1.i0 + 100*a2.i0
FROM t0 AS a0, t0 AS a1, t0 AS a2;

ANALYZE TABLE t1,t2;

EXPLAIN SELECT *
FROM t1 JOIN t2 ON t1.i1=t2.i1
WHERE t2.i2 > 3
ORDER BY t1.i1 LIMIT 20;

FLUSH STATUS;

SELECT *
FROM t1 JOIN t2 ON t1.i1=t2.i1
WHERE t2.i2 > 3
ORDER BY t1.i1 LIMIT 20;

SHOW STATUS LIKE 'Sort%';

DROP TABLE t0, t1, t2;

Suggested fix:
Fix the execution of the query to use file sort which is the correct execution strategy in this case.
[13 Feb 2015 21:49] Paul DuBois
Noted in 5.7.6 changelog.

For some queries with LIMIT, EXPLAIN could indicate that execution 
would be done using filesort, but execution actually was done using
an index read.