| Bug #38842 | Fix for 25951 seems incorrect | ||
|---|---|---|---|
| Submitted: | 18 Aug 2008 10:17 | Modified: | 12 Dec 2008 2:39 |
| Reporter: | Pavamanaprasad Athani | ||
| Status: | Closed | ||
| Category: | 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 | Target Version: | 5.0.74+ |
| Tags: | boolean, full text, ignore index, force index, index hints, regression | ||
| Triage: | Triaged: D3 (Medium) / R2 (Low) / E2 (Low) | ||
[18 Aug 2008 10:39]
Sveta Smirnova
Thank you for the report. Please provide output of SHOW CREATE TABLE feature_name
[18 Aug 2008 11: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 15:14]
Sveta Smirnova
Thank you for the feedback. Please provide output of SHOW CREATE TABLE geofeature also.
[18 Aug 2008 15: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 16: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 9: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 9: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 9: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 11:01]
Sveta Smirnova
Thank you for the data provided. Verified as described.
[7 Nov 2008 20: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 12: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 13: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 11: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 18: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 14: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 11: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 12: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 2: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 12: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 13: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 17: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 20:52]
Paul DuBois
Noted in 5.0.72sp1 changelog.
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.