MySQL Bugs: #1560: EXPLAIN shows wrong access type for the table

Bug #1560	EXPLAIN shows wrong access type for the table
Submitted:	15 Oct 2003 6:56	Modified:	19 Sep 2012 8:18
Reporter:	Konstantin Osipov (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	5.1	OS:	Any (all)
Assigned to:	Evgeny Potemkin	CPU Architecture:	Any

Description:
This is echo of bug #1274. 
The problem is that for this query EXPLAIN shows that ALL access type is used (which would be good), but really reverse index scan takes place.

mysql> SELECT SQL_CALC_FOUND_ROWS race_name FROM races WHERE race_name
LIKE
'%Madison%' ORDER BY race_date DESC LIMIT 0,100;
Empty set (38.59 sec)

mysql> explain SELECT SQL_CALC_FOUND_ROWS race_name FROM races WHERE race_name
LIKE '%Madison%' ORDER BY race_date DESC LIMIT 0,100;
+-------+------+---------------+------+---------+------+--------+---------------
--------------+
| table | type | possible_keys | key  | key_len | ref  | rows   | Extra
             |
+-------+------+---------------+------+---------+------+--------+---------------
--------------+
| races | ALL  | NULL          | NULL |    NULL | NULL | 505821 | Using where;
Using filesort |
+-------+------+---------------+------+---------+------+--------+---------------
--------------+
1 row in set (0.00 sec)

As you might see the explain is "good" in this case while speed is the same as
in case of using index access mode.

How to repeat:
use 'races' table from bug #1274

the problem is that we change filesort to index scan in the last moment in mysql_select.
Seems to be necessary to change how explain works significantly to fix the bug.
Note that SQL_CALC_FOUND_ROWS now should force filesort, so proper query to spot bug should 
be 

SELECT race_name FROM races WHERE race_name
LIKE'%Madison%' ORDER BY race_date DESC LIMIT 0,100;

Bug is in 4.0 but becasue of the big change required to fix this we will look at fixing this in the 4.1.x series instead

Please don't reassign this bug.
We can't fix it easily, to fix it we need to redesign EXPLAIN processing.

Fixed by wl5558.

Noted in 5.6.5 changelog.

Reporting of how to sort a result set in EXPLAIN has been improved
for some statements. This sorting decision could be reported
incorrectly, causing Using filesort or Using temporary to be reported
when they should not have been or vice versa. This could occur for
statements that included index hints, that had the form SELECT
SQL_BIG_RESULT ... GROUP BY, that used SQL_CALC_FOUND_ROWS with
LIMIT, or that used GROUP BY, ORDER BY, and LIMIT.