| Bug #36710 | problem in full text search and subquery | ||
|---|---|---|---|
| Submitted: | 14 May 2008 10:48 | Modified: | 9 Jul 2008 17:13 |
| Reporter: | d sharma | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: FULLTEXT search | Severity: | S5 (Performance) |
| Version: | 5.0, 5.1.23 | OS: | Windows |
| Assigned to: | CPU Architecture: | Any | |
[14 May 2008 13:59]
Valeriy Kravchuk
Thank you for a problem report. Please, send EXPLAIN results for both queries and SHOW CREATE TABLE results for all tables used. What exact version, 5.0.x, do you use?
[21 May 2008 12:28]
d sharma
table defs
Attachment: a123.sql (application/octet-stream, text), 8.31 KiB.
[22 May 2008 18:59]
Valeriy Kravchuk
What exact version, 5.0.x, do you use? Please, try to repeat with one of the latest, 5.0.51 at least, and inform about the results.
[23 May 2008 5:24]
d sharma
i have tested on mysql 5.1.23 also and the same results
e.g
(select caseid from cnotes where match(catchnote) against('women officer' in boolean mode))
union
(select caseid from cnotes where match(headnote) against('women officer' in boolean mode))
takes just 0.407 secs
but
(select caseid from cnotes where match(catchnote,headnote) against('women officer' in boolean mode))
takes 3.093 secs............. about 10 times slow, and that is a big degradation
i havent tried with three fields, but looks like still worse..
....btw thanks for the fast and prompt reply
[24 May 2008 5:47]
Valeriy Kravchuk
Please, compare (and send) the results of
EXPLAIN (select caseid from cnotes where match(catchnote) against('women officer' in boolean mode))
union
(select caseid from cnotes where match(headnote) against('women officer' in boolean mode))\G
and
EXPLAIN (select caseid from cnotes where match(catchnote,headnote) against('women officer' in boolean mode))
MySQL server can not use 2 FULLTEXT indexes to access one table in one SELECT, AFAIR.
[26 May 2008 5:31]
d sharma
according to the manual , multiple cloums can be selected for a full text search. (but the speed of search gets degraded....why?). MATCH (col1,col2,...) AGAINST (expr [search_modifier]) Full-text searching is performed using MATCH() ... AGAINST syntax. MATCH() takes a comma-separated list that names the columns to be searched. AGAINST takes a string to search for, and an optional modifier that indicates what type of search to perform. The search string must be a literal string, not a variable or a column name.
[26 May 2008 5:35]
d sharma
explain sample
Attachment: exp103.jpg (image/jpeg, text), 97.86 KiB.
[11 Jun 2008 7:01]
d sharma
Valeriy Kravchuk, havent heard from u since long. what happened to my bug and its solution. waiting to hear from you.....
[9 Jul 2008 17:13]
Valeriy Kravchuk
This is not a bug. As you can see based on your EXPLAIN results, index is NOT used for the query with multiple columns matched. Please, read the manual, http://dev.mysql.com/doc/refman/5.0/en/fulltext-restrictions.html: "The MATCH() column list must match exactly the column list in some FULLTEXT index definition for the table, unless this MATCH() is IN BOOLEAN MODE. Boolean-mode searches can be done on non-indexed columns, although they are likely to be slow."
Description: (total records in cnote=23000,gtxt=21000,gcase=21000) the tables are myisam with fulltext indexes and repaired ================================= Select caseid from cnote where match(hnote) against('+TkrO*' IN BOOLEAN MODE) UNION Select caseid from cnote where match(cnote) against('+TkrOe*' IN BOOLEAN MODE) union Select caseid from gtxt where match(jfulltext) against('+TkrOe' IN BOOLEAN MODE) ////the output is in 0.50 secs ----------------- Select caseid from cnote where match(hnote,cnote) against('+TkrO*' IN BOOLEAN MODE) union Select caseid from gtxt where match(jfulltext) against('+TkrOe' IN BOOLEAN MODE) ///the output is in 3.35 secs even though both the queries are same ******************************************************** SELECT CaseId FROM gcase WHERE CaseId in (Select caseid from cnote where match(hnote,cnote) against('TkrOe' IN BOOLEAN MODE) union Select caseid from gtxt where match(jfulltext) against('Tkr44Oe' IN BOOLEAN MODE)) ///the output is in 3.55 secs ---------------------- SELECT CaseId FROM gcase WHERE CaseId in (Select caseid from cnote where match(hnote) against('TkrOe' IN BOOLEAN MODE) union (Select caseid from cnote where match(cnote) against('TkrOe' IN BOOLEAN MODE) union Select caseid from glrtxt where match(jfulltext) against('Tkr44Oe' IN BOOLEAN MODE)) ///the output takes more than 5 minutes -------------------------- SELECT a.CaseId FROM gcase a , ( Select caseid from cnote where match(hnote) against('+TkrO*' IN BOOLEAN MODE) UNION Select caseid from cnote where match(cnote) against('+TkrOe*' IN BOOLEAN MODE) union Select caseid from gtxt where match(jfulltext) against('+TkrOe' IN BOOLEAN MODE)) b where a.caseid=b.caseid ///the output takes just 0.56 secs why are the timings so different??? How to repeat: please run the above script to reproduce