Bug #38842 Fix for 25951 seems incorrect
Submitted: 18 Aug 2008 8:17 Modified: 12 Dec 2008 1:39
Reporter: Pavamanaprasad Athani Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.0.45, 4.1, 5.0, 5.1, 6.0 bzr OS:Any
Assigned to: Sergey Vojtovich
Tags: boolean, force index, full text, ignore index, index hints, regression
Triage: Triaged: D3 (Medium) / R2 (Low) / E2 (Low)

[18 Aug 2008 8:17] Pavamanaprasad Athani
Description:
I was earlier using MySQL Community 5.0.37 version where a query of mine was working as expected.  Due to fix 25951, this query is behaving un-expectedly in 5.0.45
I have a query like this:

explain select * from feature_name fn force index(gf),geofeature g where match (fn.info) against ('+4' in boolean mode) and fn.geofeature_id = g.id and g.object_id = 7196;

In 5.0.37, "force index(gf)" was honoured which is the correct behaviour.

Fix 25951 seems to have reversed this behaviour and now in 5.0.45, "force index(gf)" is being ignored and it is using the full-text index on fn.info. This is incorrect behavior.

I am going by the statement in MySQL 5.0 manual:
"
Boolean full-text searches have these characteristics:
...
They can work even without a FULLTEXT index, although a search executed in this fashion would be quite slow. 
"

Since the design of my query is such that I am expecting to narrow down the result set quite a bit by the index 'gf' (which I am using in force index hint), I am okay with slowness of boolean search within the restricted result set. 

Now, after you have fixed 25951, this query of mine has broken (because of the assumption being broken), and the query is turning out be extremely slow as it is using an un-intended index (my data is such that full text index in this case would return a long result list, upon which the scan is performed to match the other criteria in the query, which is going to be slow). 

I don't think it is correct to ignore "force index" hint in this case (atleast when I am using a boolean mode full text where clause).

Can you please help me with any work around that exists for this issue?

How to repeat:
Please refer to 25951 itself for some of the cases where this problem may occur.
[18 Aug 2008 8:39] Sveta Smirnova
Thank you for the report.

Please provide output of SHOW CREATE TABLE feature_name
[18 Aug 2008 9:10] Pavamanaprasad Athani
CREATE TABLE  `feature_name` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `geofeature_id` int(10) unsigned NOT NULL,
  `info` text character set latin1,
  PRIMARY KEY  USING BTREE (`id`),
  KEY `gf` (`geofeature_id`),
  FULLTEXT KEY `ftinfo` (`info`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
[18 Aug 2008 13:14] Sveta Smirnova
Thank you for the feedback.

Please provide output of SHOW CREATE TABLE geofeature also.
[18 Aug 2008 13:28] Pavamanaprasad Athani
Sorry. Forgot that.
Here it is:

CREATE TABLE  `geofeature` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `object_id` int(10) unsigned NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `obj` (`object_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
[18 Aug 2008 14:02] Sveta Smirnova
Thank you for the feedback.

I can not repeat described behavior with test data. Please try current version 5.0.67 and if problem still exists provide output of EXPLAIN and, if possible, dump of these 2 tables.
[19 Aug 2008 7:03] Pavamanaprasad Athani
Restore this file to create data for reproducing the bug

Attachment: indextest 20080819 1229.sql (text/plain), 43.06 KiB.

[19 Aug 2008 7:09] Pavamanaprasad Athani
I have attached the sql file which you can restore to create the data required for reproducing the buggy behaviour.

After recreating the data, use this query to reproduce the issue:

explain select * from feature_name fn force index(gf) ,geofeature g where match (fn.info) against ('+4' in boolean mode) and fn.geofeature_id = g.id and g.object_id = 1;

You will see that for table 'fn', key 'ftinfo' is being used in spite of using 'force index(gf)' for it.
[19 Aug 2008 7:12] Pavamanaprasad Athani
Here is the output of EXPLAIN:

id select_type table type      possible_keys    key key_len ref   rows Extra 
1 SIMPLE         g   const     PRIMARY,obj      obj    4    const  1  (null) 
1 SIMPLE         fn  fulltext  gf,ftinfo        ftinfo 0    (null) 1  Using where
[19 Aug 2008 9:01] Sveta Smirnova
Thank you for the data provided.

Verified as described.
[7 Nov 2008 19:37] Sergey Vojtovich
Risk and effort to fix are set with assumption that we will re-enable index hints for boolean mode searches. If this is not the case it may be not that trivial to fix.
[10 Nov 2008 11:27] Sergey Vojtovich
Fulltext indexes are very different from regular indexes. The decision whether to use fulltext index is done earlier than the decision for regular indexes. That means to make true support for index hints for fulltext indexes would require a huge rewrite of fulltext engine at SQL layer.

Anyway I'd agree that for boolean mode searches ignore fulltext index and force regular index are useful hints.

What we could do is to implement special handling of index hints for fulltext indexes with the following characteristic:
- all index hints will still be ignored for NLQ mode searches - it cannot work without index;
- index hints FOR ORDER BY and FOR GROUP BY will still be ignored for fulltext indexes;
- USE/FORCE index hints will still be ignored for fulltext indexes;
- BOOLEAN mode searches will honor IGNORE INDEX hint;
- BOOLEAN mode searches will honor FORCE/USE INDEX hints for regular indexes.

This sounds like a good compromise and should solve all the problems mentioned in this bug report.
[10 Nov 2008 12:46] 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:

  http://lists.mysql.com/commits/58332

2705 Sergey Vojtovich	2008-11-10
      BUG#38842 - Fix for 25951 seems incorrect
      
      Prior to fix for bug 25951 index hints were honored for fulltext
      indexes, though most of them were not working as expected. With
      fix for bug 25951 index hints have no effect for fulltext
      indexes, which is correct, but doesn't allow users to provide
      some really useful hints to optimizer.
      
      Though it is not that trivial to make true index hints support
      for fulltext indexes, this patch implements special index hints
      support for fulltext indexes with the following characteristics:
      - all index hints are still ignored for NLQ mode searches -
        it cannot work without index;
      - index hints FOR ORDER BY and FOR GROUP BY are still ignored
        for fulltext indexes;
      - USE/FORCE index hints are still ignored for fulltext indexes;
      - BOOLEAN mode searches honor IGNORE INDEX hint;
      - BOOLEAN mode searches honor FORCE/USE INDEX hints for regular
        indexes.
[11 Nov 2008 10:13] 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:

  http://lists.mysql.com/commits/58434

2712 Sergey Vojtovich	2008-11-11
      BUG#38842 - Fix for 25951 seems incorrect
      
      With fix for bug 25951 index hints are ignored for fulltext
      searches, as handling of fulltext indexes is different from
      handling regular indexes. Meaning it is not possible to
      implement true index hints support for fulltext indexes within
      the scope of current fulltext architecture.
      
      The problem is that prior to fix for bug 25951, some useful
      index hints still could be given for boolean mode searches.
      
      This patch implements special index hints support for fulltext
      indexes with the following characteristics:
      - all index hints are still ignored for NLQ mode searches -
        it cannot work without an index;
      - for 5.1 and up index hints FOR ORDER BY and FOR GROUP BY are
        still ignored for fulltext indexes;
      - boolean mode searches honor USE/FORCE/IGNORE INDEX hints;
      - as opposed to index hints for regular indexes, index hints
        for fulltext BOOLEAN mode searches affect the usage of the
        index for the whole query.
[30 Nov 2008 17:01] Sergey Petrunya
The page http://dev.mysql.com/doc/refman/5.1/en/index-hints.html says "Index hints do not work for FULLTEXT indexes".  Please make sure to update that manual chapter when this fix is pushed.
[2 Dec 2008 13:02] Bugs System
Pushed into 5.0.74  (revid:svoj@mysql.com-20081111091051-54pr96nf1z2s30gx) (version source revid:svoj@mysql.com-20081118111052-mo4zszzz1itieaan) (pib:5)
[8 Dec 2008 10:22] Bugs System
Pushed into 5.1.31  (revid:svoj@mysql.com-20081111091051-54pr96nf1z2s30gx) (version source revid:patrick.crews@sun.com-20081126180318-v685u61mpgoc176x) (pib:5)
[8 Dec 2008 11:33] Bugs System
Pushed into 6.0.9-alpha  (revid:svoj@mysql.com-20081111091051-54pr96nf1z2s30gx) (version source revid:ingo.struewing@sun.com-20081121151447-dtf2ofz2ys0zqed1) (pib:5)
[12 Dec 2008 1:39] Paul Dubois
Noted in 5.0.74, 5.1.31, 6.0.9 changelogs, and index-hints section.

For 5.0:

For natural language mode searches, index hints are silently ignored.
For example, IGNORE INDEX(i) is ignored with no warning and the 
index is still used.

For boolean mode searches, index hints are honored.

For 5.1+:

For natural language mode searches, index hints are silently ignored.
For example, IGNORE INDEX(i) is ignored with no warning and the
index is still used.

For boolean mode searches, index hints with FOR ORDER BY or FOR
GROUP BY are silently ignored. Index hints with FOR JOIN or no FOR
modifier are honored. In contrast to how hints apply for non-FULLTEXT
searches, the hint is used for all phases of query execution (finding
rows and retrieval, grouping, and ordering). <i>This is true even
if the hint is given for a non-FULLTEXT index.</i>

For example, the following two queries are equivalent:

SELECT * FROM t
  USE INDEX (index1)
  IGNORE INDEX (index1) FOR ORDER BY
  IGNORE INDEX (index1) FOR GROUP BY
  WHERE ... IN BOOLEAN MODE ... ;

SELECT * FROM t
  USE INDEX (index1)
  WHERE ... IN BOOLEAN MODE ... ;
[19 Jan 2009 11:21] Bugs System
Pushed into 5.1.31-ndb-6.2.17 (revid:tomas.ulin@sun.com-20090119095303-uwwvxiibtr38djii) (version source revid:tomas.ulin@sun.com-20090108105244-8opp3i85jw0uj5ib) (merge vers: 5.1.31-ndb-6.2.17) (pib:6)
[19 Jan 2009 12:59] Bugs System
Pushed into 5.1.31-ndb-6.3.21 (revid:tomas.ulin@sun.com-20090119104956-guxz190n2kh31fxl) (version source revid:tomas.ulin@sun.com-20090119104956-guxz190n2kh31fxl) (merge vers: 5.1.31-ndb-6.3.21) (pib:6)
[19 Jan 2009 16:05] Bugs System
Pushed into 5.1.31-ndb-6.4.1 (revid:tomas.ulin@sun.com-20090119144033-4aylstx5czzz88i5) (version source revid:tomas.ulin@sun.com-20090119144033-4aylstx5czzz88i5) (merge vers: 5.1.31-ndb-6.4.1) (pib:6)
[5 Feb 2009 19:52] Paul Dubois
Noted in 5.0.72sp1 changelog.