Re: FTS query questions

Hi!

On Sep 18, Sunny Bains wrote:
> 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,
...
> 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?

Probably not. Perhaps condition pushdown could help here, but I am not sure about it.

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

No. If the index is NO_SUCH_KEY, then MyISAM doesn't use indexes. If you do EXPLAIN you'll see that optimizer does a full table scan.

> MySQL query output below for my reasoning.
>
> 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)

Probably too short word, less that ft_min_word_len.

Regards / Mit vielen Grüssen,
Sergei

-- 
   __  ___     ___ ____  __
  /  |/  /_ __/ __/ __ \/ /   Sergei Golubchik 
 / /|_/ / // /\ \/ /_/ / /__  Principal Software Developer
/_/  /_/\_, /___/\___\_\___/  MySQL GmbH, Dachauer Str. 37, D-80335 München
       <___/                  Geschäftsführer: Kaj Arnö - HRB München 162140

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

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