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.