mysql fulltext index is used for MATCH() AGAINST but not for =

Question

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?

Frank Heikens Frank Heikens · Accepted Answer · 2011-02-24T11:15:15

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.

mysql fulltext index is used for MATCH() AGAINST but not for =

1 Answers