MySQL Bugs: #25951: ignore/use index does not work with fulltext

Bug #25951	ignore/use index does not work with fulltext
Submitted:	30 Jan 2007 16:13	Modified:	8 May 2007 1:06
Reporter:	Martin Friebe (Gold Quality Contributor) (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.1.22 and 5.0.33	OS:	Any (*)
Assigned to:	Sergey Vojtovich	CPU Architecture:	Any
Tags:	fulltext, ignore index, qc, use index

Description:
From the Documentation

http://dev.mysql.com/doc/refman/5.0/en/join.html
USE INDEX, IGNORE INDEX, and FORCE INDEX affect only which indexes are used when MySQL decides how to find rows in the table and how to do the join. They do not affect whether an index is used when resolving an ORDER BY or GROUP BY.

This appears true for normal indexes only. Using those with a fulltext index affects the usage of the index for the whole query. This is an index not available due to force/ignore/use cannot be used at all, not even within the select/data part of the query

Using the table from how to repeat:

A query on c (btree) uses the ignored index for ordering.
 explain select c from fc1 ignore index(c) order by c limit 2;

but trying the same with a fulltext index:
 explain select a from fc1 ignore index(a) order by match (a) against ('order') limit 2;
 ERROR 1191 (HY000): Can't find FULLTEXT index matching the column list

This is even if the index would not be used to order the data, and even if the data would be retrieved as tablescan => The fulltext operation should still work.

The same applies to using "match" as an expression in the select part
 explain select a, match (a) against ('order') from fc1 ignore index(a);
there is no where contidition, the ignore index  is meaningless. There should always be a tablescan.

I would understand the doc, that it should even still work in the where condition. The following should do a table scan, and evaluate the "match" for each row:
 explain select a from fc1 ignore index(a) where  match (a) against ('order');

This would also allow to use the use/ignore/force index to decide between 2 fulltext indexes, if the wehere condition offers that choice. See how to repeat

How to repeat:
drop table if exists fc1;
create table fc1 (a char(100), b char(100), c int, fulltext (a), fulltext(b), index (c));
insert into fc1 values ('wording','wording',1), ('about','about',4), ('other','other',6), ('order','order',7);

explain select c from fc1 ignore index(c) order by c limit 2;

explain select a from fc1 ignore index(a) order by match (a) against ('order') limit 2;
explain select a from fc1 ignore index(a) group by match (a) against ('order');

explain select a, match (a) against ('order') from fc1 ignore index(a);

explain select a from fc1 ignore index(a) where  match (a) against ('order');

# do what ignore index is good for, try to force the optimizer to use a specific index
explain select a,b from fc1 where  match (a) against ('order') AND  match (b) against ('order');
explain select a,b from fc1 ignore index(a) where  match (a) against ('order') AND  match (b) against ('order');
explain select a,b from fc1 ignore index(b) where  match (a) against ('order') AND  match (b) against ('order');

drop table fc1;

Suggested fix:
-

Thank you for the bug report.

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/commits/24400

ChangeSet@1.2630, 2007-04-13 02:31:34+05:00, svoj@mysql.com +3 -0
  BUG#25951 - ignore/use index does not work with fulltext
  
  IGNORE/USE/FORCE INDEX hints were honored when choosing FULLTEXT
  index.
  
  With this fix these hints are ignored. For regular indexes we may
  perform table scan instead of index lookup when IGNORE INDEX was
  specified. We cannot do this for FULLTEXT in NLQ mode.

Pushed into 5.1.18-beta

Pushed into 5.0.42

Pushed into 4.1.23

Noted in 4.1.23, 5.0.42, 5.1.18 changelogs.

Index hints (USE INDEX, IGNORE INDEX, FORCE INDEX) cannot be used 
with FULLTEXT indexes, but were not being ignored.