MySQL Bugs: #12414: index merge optimization doesn't work with FULLTEXT indexes

Bug #12414	index merge optimization doesn't work with FULLTEXT indexes
Submitted:	6 Aug 2005 14:21	Modified:	14 Sep 2005 19:55
Reporter:	Gleb Paharenko	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S4 (Feature request)
Version:	5.0.10	OS:	Linux (Linux)
Assigned to:		CPU Architecture:	Any

Description:
It seems that index merge optimization doesn't work with FULLTEXT indexes

How to repeat:
mysql> show create table RESOURCE\G;
*************************** 1. row ***************************
       Table: RESOURCE
Create Table: CREATE TABLE `RESOURCE` (
  `ID` int(11) NOT NULL auto_increment,
  `TITLE` varchar(255) default NULL,
  PRIMARY KEY  (`ID`),
  KEY `TITLE_2` (`TITLE`),
  FULLTEXT KEY `TITLE` (`TITLE`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

I have about 2k rows in table to force optimizer to use indexes. I uploaded the data
in file resource.sql to ftp.mysql.com/pub/upload. Most of them just generated from the script
and you can fill the table with your own data. Here are the SELECTS which shows the problem:

mysql>  select ID,TITLE FROM RESOURCE WHERE ID=1 OR TITLE='fred';
+------+----------+
| ID   | TITLE    |
+------+----------+
|    1 | ab  baaa |
| 2006 | fred     |
+------+----------+

mysql> explain  select ID,TITLE FROM RESOURCE WHERE ID=1 OR TITLE='fred'\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: RESOURCE
         type: index_merge
possible_keys: PRIMARY,TITLE_2,TITLE
          key: PRIMARY,TITLE_2
      key_len: 4,258
          ref: NULL
         rows: 2
        Extra: Using union(PRIMARY,TITLE_2); Using where

We set index merge optimization is used. Now we will change the query so it'll use a Full-Text search:

mysql>  select ID,TITLE FROM RESOURCE WHERE ID=1 OR MATCH(TITLE) AGAINST('fred' in Boolean mode);
+------+-----------+
| ID   | TITLE     |
+------+-----------+
|    1 | ab  baaa  |
|    2 | a fred b  |
|    3 | c fred gs |
| 2005 | a fred b  |
| 2006 | fred      |
+------+-----------+

mysql> explain select ID,TITLE FROM RESOURCE WHERE ID=1 OR MATCH(TITLE) AGAINST('fred' in Boolean mode)\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: RESOURCE
         type: ALL
possible_keys: PRIMARY
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2006
        Extra: Using where

A full table scan is used, which is not good.  By the way if I change the query so it uses UNION indexes are used:

mysql> explain (select ID,TITLE FROM RESOURCE WHERE ID=1) UNION (SELECT ID,TITLE FROM RESOURCE WHERE MATCH(TITLE) AGAINST('fred' in Boolean mode));
+----+--------------+------------+----------+---------------+---------+---------+-------+------+-------------+
| id | select_type  | table      | type     | possible_keys | key     | key_len | ref   | rows | Extra       |
+----+--------------+------------+----------+---------------+---------+---------+-------+------+-------------+
|  1 | PRIMARY      | RESOURCE   | const    | PRIMARY       | PRIMARY | 4       | const |    1 |             |
|  2 | UNION        | RESOURCE   | fulltext | TITLE         | TITLE   | 0       |       |    1 | Using where |
|NULL | UNION RESULT | <union1,2> | ALL      | NULL          | NULL    | NULL    | NULL  | NULL |             |
+----+--------------+------------+----------+---------------+---------+---------+-------+------+-------------+

Suggested fix:
Make FULLTEXT indexes to be allowed in index merge optimization or document it in restrictions at:
  http://dev.mysql.com/doc/mysql/en/fulltext-restrictions.html

Hi! it's in TODO:
http://dev.mysql.com/doc/mysql/en/fulltext-todo.html

Hi!

There is a quoting from:
  http://dev.mysql.com/doc/mysql/en/fulltext-todo.html

I'm sorry (it is a bit not clear for me) - but, could you tell me, where is exactly said about index merge optimization? I think it is not a phrase about MERGE tables (because index merge optimization and MERGE tables are different things), maybe it is 
"Impoved performance of FULLTEXT searches'? 

 "     Improved performance for all FULLTEXT operations.
      Proximity operators.
      Support for “always-index words.” These could be any strings the user wants to treat as words, such as “C++”, “AS/400”, or “TCP/IP”.
      Support for full-text search in MERGE tables.
      Support for the ucs2 character set.
      Make the stopword list dependent on the language of the dataset.
      Stemming
      Generic user-suppliable UDF preparser.
      Make the model more flexible (by adding some adjustable parameters to FULLTEXT in CREATE TABLE and ALTER TABLE statements)."

I've ment:
  http://dev.mysql.com/doc/mysql/en/index-merge-optimization.html

Not:
  http://dev.mysql.com/doc/mysql/en/merge-storage-engine.html

I see. Sorry for misunderstanding. Let me check.

- This is a request for an optimization that does not look to be widely used.

By the above reason I move the bug to 'To be fixed later'.
Product management will decide in what version a fix for this problem appears.