MySQL Bugs: #14037: It seems MySQL doesn't use indexes with fulltext in boolen mode if I use OR

Bug #14037	It seems MySQL doesn't use indexes with fulltext in boolen mode if I use OR
Submitted:	14 Oct 2005 19:11	Modified:	25 Jan 2006 0:02
Reporter:	Wiktor Ihárosi	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S4 (Feature request)
Version:	MySQL 4.1.11-Debian_4sarge2-log, mysql-5	OS:	Linux (Linux, Windows)
Assigned to:	Sergey Vojtovich	CPU Architecture:	Any

Description:
If I use this query:

EXPLAIN SELECT * 
FROM `content_langs` 
WHERE 
    MATCH (lead) AGAINST ('alma' IN BOOLEAN MODE)
    OR 
    MATCH (content) AGAINST ('alma' IN BOOLEAN MODE)

I expected MySQL to show me how use the indexes, but it tells me, there is no index... Upps!

But if I run this query:

EXPLAIN SELECT * 
FROM `content_langs` 
WHERE 
    MATCH (lead) AGAINST ('alma' IN BOOLEAN MODE)
    AND
    MATCH (content) AGAINST ('alma' IN BOOLEAN MODE)

It's okay, there is index!

How to repeat:
Run the above mentioned two queries in this table:

CREATE TABLE `content_langs` (
  `id` int(11) NOT NULL auto_increment,
  `content_id` int(11) NOT NULL default '0',
  `lang` varchar(2) NOT NULL default '',
  `uptitle` varchar(255) NOT NULL default '',
  `title` varchar(255) NOT NULL default '',
  `subtitle` varchar(255) NOT NULL default '',
  `shorttitle` varchar(255) NOT NULL default '',
  `url` varchar(255) NOT NULL default '',
  `lead` text NOT NULL,
  `content` text NOT NULL,
  `modify_date` datetime default '0000-00-00 00:00:00',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `content_id` (`content_id`,`lang`),
  KEY `lang` (`lang`),
  KEY `url` (`url`),
  KEY `updatedate` (`modify_date`),
  FULLTEXT KEY `uptitle` (`uptitle`),
  FULLTEXT KEY `title` (`title`),
  FULLTEXT KEY `shorttitle` (`shorttitle`),
  FULLTEXT KEY `lead` (`lead`),
  FULLTEXT KEY `content` (`content`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='xxx' AUTO_INCREMENT=0;

Suggested fix:
I don't know... :(

mysql> EXPLAIN SELECT * 
    -> FROM `content_langs` 
    -> WHERE 
    -> MATCH (lead) AGAINST ('alma' IN BOOLEAN MODE)
    -> OR 
    -> MATCH (content) AGAINST ('alma' IN BOOLEAN MODE)\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: content_langs
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 0
        Extra: Using where
1 row in set (0.01 sec)

mysql> 
mysql> EXPLAIN SELECT * 
    -> FROM `content_langs` 
    -> WHERE 
    -> MATCH (lead) AGAINST ('alma' IN BOOLEAN MODE)
    -> AND
    -> MATCH (content) AGAINST ('alma' IN BOOLEAN MODE)\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: content_langs
         type: fulltext
possible_keys: lead,content
          key: lead
      key_len: 0
          ref: 
         rows: 1
        Extra: Using where
1 row in set (0.01 sec)

MySQL doesn't support "index merge" join method on fulltext indexes yet.
Since it is rather complicated task it must go via WL and documented as
known limitation.

Thank you for your bug report. This issue has been addressed in the
documentation. The updated documentation will appear on our website
shortly, and will be included in the next release of the relevant
product(s).

Additional info:

Updated Optimization chapterof  5.0/5.1 Manual as suggested by developer.

Note that 4.1 doesn't support index_merge algorithm.