Hi,
I have two question, one regarding the (MyISAM) query results and the
other regarding the reason why MySQL passes NO_SUCH_KEY when it calls
the engine for queries that specify multiple FTS indexes to search.
Given the following table def:
CREATE TABLE t (
id int unsigned NOT NULL AUTO_INCREMENT,
title varchar(255) NOT NULL DEFAULT '',
text1 mediumtext NOT NULL,
text2 mediumtext NOT NULL,
PRIMARY KEY (`id`),
FULLTEXT (title),
FULLTEXT (text1),
FULLTEXT (text2)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
*First question*
and then when we run the following query on the table:
SELECT * FROM t
WHERE MATCH (title, text1)
AGAINST ('One' IN BOOLEAN MODE);
MySQL calls the engine, with the FTS index to search set to NO_SUCH_KEY,
#0 ft_init_boolean_search (info=0x8b93e98, keynr=4294967295,
query=0x8b8f4d0 "Two", query_len=3, cs=0x86afba0)
at ft_boolean_search.c:534
#1 0x0846073b in ft_init_search (flags=1, info=0x8b93e98, keynr=4294967295,
query=0x8b8f4d0 "Two", query_len=3, cs=0x86afba0, record=0x8b93090 "\004")
at ft_static.c:64
#2 0x08469a74 in ha_myisam::ft_init_ext (this=0x8b92f80, flags=1,
inx=4294967295, key=0x8b8f4e0) at ha_myisam.h:89
#3 0x08153a6b in Item_func_match::init_search (this=0x8b8f540, no_order=false)
at item_func.cc:4885
#4 0x0821d72a in init_ftfuncs (thd=0x8b61f18, select_lex=0x8b62f9c,
no_order=false) at sql_base.cc:7327
#5 0x082513c6 in JOIN::optimize (this=0x8b8f6c0) at sql_select.cc:1195
I think MySQL should pass in the ids of the FTS indexes that need to be
searched. Is it possible to get hold of this information from somewhere?
*Second question*
and MyISAM from what I can understand then looks up the terms in the
query starting from the first FTS index defined on the table. See
MySQL query output below for my reasoning.
Is my conclusion above correct ?
mysql> select * from t;
+----+--------+-------+-------+
| id | title | text1 | text2 |
+----+--------+-------+-------+
| 1 | Title1 | One | One |
| 2 | Title2 | Two | One |
| 3 | Title3 | Three | One |
| 4 | Title4 | Four | One |
+----+--------+-------+-------+
mysql> SELECT * FROM t WHERE MATCH (title, text1)
AGAINST ('One' IN BOOLEAN MODE);
Empty set (4.09 sec)
I get no match for the above query but when I run the following
queries I get a match:
mysql> SELECT * FROM t WHERE MATCH (title, text1)
AGAINST ('Title1 One' IN BOOLEAN MODE);
+----+--------+------+-------+
| id | title | text | text2 |
+----+--------+------+-------+
| 1 | Title1 | One | One |
+----+--------+------+-------+
1 row in set (3.12 sec)
mysql> SELECT * FROM t WHERE MATCH (title, text1)
AGAINST ('Title1' IN BOOLEAN MODE);
+----+--------+------+-------+
| id | title | text | text2 |
+----+--------+------+-------+
| 1 | Title1 | One | One |
+----+--------+------+-------+
1 row in set (2.44 sec)
Regards,
-sunny
