MySQL Bugs: #1300: FORCE INDEX does not work in some cases

Bug #1300	FORCE INDEX does not work in some cases
Submitted:	16 Sep 2003 8:18	Modified:	8 Oct 2003 23:44
Reporter:	Peter Zaitsev (Basic Quality Contributor)	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.0.15	OS:	Any (all)
Assigned to:	Ramil Kalimullin	CPU Architecture:	Any

Description:
Force index does not work, if one would want MySQL to prefer full index scan instead of full table scan and file sort.

mysql> explain select  * from ign force 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)

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  * from ign force index(i) order by i;
explain select  sql_calc_found_rows * from ign force index(i) order by i limit 1;

Monty said (see bug #1296):
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

So, I close this bug.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/29816