Hi,
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)
(gdb) bt
#0 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
#1 0x08469a74 in ha_myisam::ft_init_ext (this=0x8b8eec0, flags=1,
inx=4294967295, key=0x8b8b918) at ha_myisam.h:89
#2 0x08153a6b in Item_func_match::init_search (this=0x8b8b9a0, no_order=false)
at item_func.cc:4885
#3 0x0821d72a in init_ftfuncs (thd=0x8b5def0, select_lex=0x8b5ef74,
no_order=false) at sql_base.cc:7327
#4 0x082513c6 in JOIN::optimize (this=0x8b8bbc0) at sql_select.cc:1195
#5 0x082532aa in mysql_select (thd=0x8b5def0, rref_pointer_array=0x8b5f078,
tables=0x8b8b190, wild_num=1, fields=@0x8b5f008, conds=0x8b8ba90,
og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0,
select_options=2147764736, result=0x8b8bbb0, unit=0x8b5ed0c,
select_lex=0x8b5ef74) at sql_select.cc:2261
#6 0x08257728 in handle_select (thd=0x8b5def0, lex=0x8b5ecb0,
result=0x8b8bbb0, setup_tables_done_option=0) at sql_select.cc:262
#7 0x081e4a00 in execute_sqlcom_select (thd=0x8b5def0, all_tables=0x8b8b190)
at sql_parse.cc:4544
#8 0x081e5e27 in mysql_execute_command (thd=0x8b5def0) at sql_parse.cc:1888
#9 0x081eda7b in mysql_parse (thd=0x8b5def0,
inBuf=0x8b8af38 "SELECT * FROM articles WHERE author = 'Author' and
published_date > '2006-01-01' and MATCH (title, abstract) AGAINST ('Title3
Abstract3' IN BOOLEAN MODE)", length=157, found_semicolon=0x8f236340)
at sql_parse.cc:5457
#10 0x081ee3ef in dispatch_command (command=COM_QUERY, thd=0x8b5def0,
packet=0x8b82f09 "SELECT * FROM articles WHERE author = 'Author' and
published_date > '2006-01-01' and MATCH (title, abstract) AGAINST ('Title3
Abstract3' IN BOOLEAN MODE)", packet_length=158) at sql_parse.cc:958
#11 0x081ef304 in do_command (thd=0x8b5def0) at sql_parse.cc:717
#12 0x081dfca5 in handle_one_connection (arg=0x8b5def0) at sql_connect.cc:1094
#13 0xb7f76240 in start_thread () from /lib/tls/i686/cmov/libpthread.so.0
#14 0xb7e893de in clone () from /lib/tls/i686/cmov/libc.so.6
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.
What am I missing here ?
Regards,
-sunny
