Re: FTS Query + Optimizer question

Hi!

On Sep 21, Sunny Bains wrote:
> Given the following table definition:
>
> CREATE TABLE articles (
> id int unsigned NOT NULL AUTO_INCREMENT,
> author varchar(255) NOT NULL,
> published_date date NOT NULL,
> title varchar(255) NOT NULL DEFAULT '',
> abstract mediumtext NOT NULL,
> PRIMARY KEY (`id`),
> INDEX author_index (author),
> INDEX published_date_index (published_date),
> FULLTEXT (title),
> FULLTEXT (abstract)
> ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
>
> and the following data:
>
> mysql> select * from articles;
> +----+--------+----------------+--------+-----------+
> | id | author | published_date | title | abstract |
> +----+--------+----------------+--------+-----------+
> | 1 | Author | 2006-01-01 | Title1 | Abstract1 |
> | 2 | Author | 2006-02-01 | Title2 | Abstract2 |
> | 3 | Author | 2007-02-01 | Title3 | Abstract3 |
> +----+--------+----------------+--------+-----------+
> 3 rows in set (0.00 sec)
>
> mysql> EXPLAIN SELECT * FROM articles WHERE author = 'Author' and
> published_date > '2006-01-01' and MATCH (title, abstract) AGAINST ('Title3
> Abstract3' IN BOOLEAN MODE);
> +----+-------------+----------+------+-----------------------------------+--------------+---------+-------+------+-------------+
> | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
> +----+-------------+----------+------+-----------------------------------+--------------+---------+-------+------+-------------+
> | 1 | SIMPLE | articles | ref | author_index,published_date_index | author_index | 257 | const | 2 | Using where |
> +----+-------------+----------+------+-----------------------------------+--------------+---------+-------+------+-------------+
> 1 row in set, 1 warning (0.00 sec)
>
>
> The above output suggests that the type is *ref* but when I run the query
> and observe using GDB I get this:
>
> Breakpoint 1, ft_init_search (flags=1, info=0x8b902d0, keynr=4294967295,
> query=0x8b8b8f8 "Title3 Abstract3", query_len=16, cs=0x86afba0,
> record=0x8b8f2a8 "\003") at ft_static.c:63
> 63 if (flags & FT_BOOL)
>
> Where keynr == NO_SUCH_KEY which implies that MyISAM will actually do
> a table scan. If my understanding is correct then this is suboptimal for
> one and secondly the EXPLAIN statement result above is wrong.

I'm sorry, I was imprecise :(
keynr == NO_SUCH_KEY does not necessarily mean table scan. You could see what the actual access method is by putting a breakpoint on actual access methods - index_search and rnd_next.

keynr == NO_SUCH_KEY means that MySQL won't do a fulltext index scan, and will give a record to the fulltext engine, which should calculate a relevance value based on record's data.

This cannot be done for natural language mode fulltext search, but works ok in boolean mode.

a record, given to the fulltext search engine, can come from the table scan, index scan, or any other access method. In your example optimizer decides to do a ref on author_index.  

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