FTS query questions

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)

*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,

Do you need help?

In addition to our free technical library, Pantek provides professional IT services to companies who utilize Open Source software: Learn more about Pantek's Technical Support offerings Place an order for 24/7/365 Technical Support here Learn more about Pantek, who we are, and what we do Chat with a Live Operator Related lists.mysql.com Links announce benchmarks bugs certification cluster commits dotnet gui-tools internals java maxdb myodbc mysql mysqldoc packagers perl plusplus replication win32 Request more information about Pantek

    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.

Do you need more help?

In addition to our free technical library, Pantek provides professional IT services to companies who utilize Open Source software: Learn more about Pantek's Technical Support offerings Place an order for 24/7/365 Technical Support here Learn more about Pantek, who we are, and what we do Chat with a Live Operator Related lists.mysql.com Links announce benchmarks bugs certification cluster commits dotnet gui-tools internals java maxdb myodbc mysql mysqldoc packagers perl plusplus replication win32 Request more information about Pantek

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)

Can we help you?

In addition to our free technical library, Pantek provides professional IT services to companies who utilize Open Source software: Learn more about Pantek's Technical Support offerings Place an order for 24/7/365 Technical Support here Learn more about Pantek, who we are, and what we do Chat with a Live Operator Related lists.mysql.com Links announce benchmarks bugs certification cluster commits dotnet gui-tools internals java maxdb myodbc mysql mysqldoc packagers perl plusplus replication win32 Request more information about Pantek

       AGAINST ('Title1' IN BOOLEAN MODE); +----+--------+------+-------+
| id | title | text | text2 |
+----+--------+------+-------+
| 1 | Title1 | One | One |
+----+--------+------+-------+
1 row in set (2.44 sec)

Regards,
-sunny

-- 
MySQL Internals Mailing List
For list archives: 
http://lists.mysql.com/internals
To unsubscribe:    
http://lists.mysql.com/internals?unsub=lists@pantek.com