Bug #1559 EXPLAIN shows wrong access type for the table

• Submitted: 15 Oct 2003 6:56
• Modified: 15 Oct 2003 7:11
• Reporter: Konstantin Osipov (OCA)
• Status: Duplicate
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 4.0
• OS: Any (all)
• Assigned to: Assigned Account
• CPU Architecture: Any

[15 Oct 2003 6:56] Konstantin Osipov

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

[15 Oct 2003 7:09] Konstantin Osipov

this is dup of #1560

[15 Oct 2003 7:11] Alexander Keremidarski

Duplicate with #1560 http://bugs.mysql.com/1560