Bug #1296 IGNORE INDEX does not work in case of ORDER BY .. LIMIT

• Submitted: 16 Sep 2003 7:17
• Modified: 19 Sep 2003 5:35
• Reporter: Peter Zaitsev (Basic Quality Contributor)
• Status: Not a Bug
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 4.0.15
• OS: Any (all)
• Assigned to: Ramil Kalimullin
• CPU Architecture: Any

[16 Sep 2003 7:17] Peter Zaitsev

Description:
It looks like IGNORE INDEX does not work in all cases. 

mysql> explain select i from ign ignore index (i) order by i limit 0,1;
+-------+-------+---------------+------+---------+------+------+-------+
| table | type  | possible_keys | key  | key_len | ref  | rows | Extra |
+-------+-------+---------------+------+---------+------+------+-------+
| ign   | index | NULL          | i    |       4 | NULL |   11 |       |
+-------+-------+---------------+------+---------+------+------+-------+
1 row in set (0.00 sec)

mysql> explain select i from ign ignore index (i) order by i;
+-------+------+---------------+------+---------+------+------+----------------+
| table | type | possible_keys | key  | key_len | ref  | rows | Extra          |
+-------+------+---------------+------+---------+------+------+----------------+
| ign   | ALL  | NULL          | NULL |    NULL | NULL |   11 | Using filesort |
+-------+------+---------------+------+---------+------+------+----------------+
1 row in set (0.00 sec)

Note index scan is used in case #1 while IGNORE INDEX is specified.

How to repeat:
CREATE TABLE ign (
  i int(10) unsigned NOT NULL default '0',
  c char(10) default NULL,
  KEY i (i)
) TYPE=MyISAM;

--
-- Dumping data for table 'ign'
--

INSERT INTO ign VALUES (1,NULL),(2,NULL),(3,NULL),(4,NULL),(5,NULL),(6,NULL),(7,NULL),(8,NULL),(9,NULL),(1,NULL),(10,NULL);

explain select i from ign ignore index (i) order by i limit 0,1;

[19 Sep 2003 5:35] Michael Widenius

Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

The IGNORE INDEX directive ONLY affects the JOIN, not how the ORDER BY is executed.

I shall ensure that the documentation is updated regarding this