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 8:56	Modified:	29 Jan 2004 7:50
Reporter:	Konstantin Osipov
Status:	Verified
Category:	Server: Optimizer	Severity:	S3 (Non-critical)
Version:	5.1	OS:	Any (all)
Assigned to:	Konstantin Osipov	Target Version:

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.