mysql fulltext index is used for MATCH() AGAINST but not for =
My table xm_c is created like this:
CREATE TABLE `xm_c` ( `category_id` char(16) NOT NULL default '', `label` char(64) NOT NULL default '', `flags` smallint(5) unsigned default NULL, `d0` date default NULL, `d1` date default NULL, `ct` int(6) unsigned default NULL, `t_update` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, PRIMARY KEY (`category_id`), **FULLTEXT KEY `label` (`label`)** ) ENGINE=MyISAM DEFAULT CHARSET=latin1 DELAY_KEY_WRITE=1;
The FULLTEXT index is NOT used in the query below:
select * from xm_c where label = 'TomCruise';
where as it is used here:
select * from xm_c where MATCH(label) AGAINST('TomCruise');
mysql> explain select * from xm_c where MATCH(label) AGAINST('TomCruise');
>
id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra
**1 | SIMPLE | xm_c | fulltext | label | label | 0 | | 1 | Using where**
mysql> explain select * from xm_c where label = 'TomCruise';
> id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra
**1 | SIMPLE | xm_c | ALL | label | NULL | NULL | NULL | 5673360 | Using where**
Can someone explain this? Shouldn't the INDEX be used in both the queries?
Is there a syntax constraint in using FULLTEXT INDICES?
A full text index can only be used in MATCH() ... AGAINST operations. An = operator is very different and has nothing to do with fts, it just can't use this type of index. More information about fts can be found in the manual.
A horse uses horseshoes, a car uses tyres. And both can bring you from A to B.
链接地址: http://www.djcxy.com/p/25078.html