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:
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.0.15 OS:Any (all)
Assigned to: Ramil Kalimullin CPU Architecture:Any

[16 Sep 2003 8:18] Peter Zaitsev
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:
  i int(10) unsigned NOT NULL default '0',
  c char(10) default NULL,
  KEY i (i)

-- 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;
[8 Oct 2003 23:44] Ramil Kalimullin
Monty said (see bug #1296):
The IGNORE INDEX directive ONLY affects the JOIN, not how the ORDER BY is
I shall ensure that the documentation is updated regarding this

So, I close this bug.
[14 Sep 2005 10:16] Bugs System
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: