MySQL EXPLAIN doesn't show 'use index' for FULLTEXT

Question

I have a basic table :

create table fullTextTest
(
 id INT(11) NOT NULL,
 superText CHAR(255) NOT NULL,
 superLongText TEXT NOT NULL,
 primary key (`id`),
 FULLTEXT KEY `superText` (`superText`),
 FULLTEXT KEY `superLongtext` (`superLongtext`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
insert into fullTextTest
 set id=1,
 superText="Hi guys, how is it goin'?",
 superLongtext="Please give me some dummy text to search on!!!"
;
show index from fullTextTest;
| fullTextTest | 0 | PRIMARY | 1 | id | A | 1 | NULL | NULL | | BTREE | |
| fullTextTest | 1 | superText | 1 | superText | NULL | NULL | NULL | NULL | | FULLTEXT | |
| fullTextTest | 1 | superLongtext | 1 | superLongText | NULL | NULL | NULL | NULL | | FULLTEXT | |

Now , let's see if MySQL is using this index correctly :

EXPLAIN select * from fullTextTest where match(superText) AGAINST ("guys" IN BOOLEAN MODE);
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------------+----------+---------------+-----------+---------+------+------+-------------+
| 1 | SIMPLE | fullTextTest | fulltext | superText | superText | 0 | | 1 | Using where |

The "Using Where" shows me that EXPLAIN don't understand FULLText.. Which is a shame, really.

Did I miss something ?

Answer 1

I can understand why this happening

You only have one row in the table.

MySQL Query Optimizer will execute a plan around this rule-of-thumb : If the number of rows needed to examine the fulfillment of a query through an index exceeds 5% of the total of number of rows, the Query Optimizer will not use the index and will go with a table scan or an index scan instead.

How many rows in the table ? One

How many rows are indexed ? One

Since you will be examining 100% of the rows, a keyed lookup will not chosen. The query will be executed. You simply do not have enough rows to justify getting an index involved in the search. This applies in general for anything indexed. FULLTEXT indexes tends to get thrown the Query Optimizer under the bus at the most inconvenient times. I wrote about that back in Jan 26, 2012.

Try loading more rows into the table, at least 21 rows, and try again.

• Ant: I have now 30 rows in my table, and still the same issue.
• Ant: I read many of your comments on FULLTEXT indexes yesterday, and found many of the links usefull.
• Rick James: Though valid, the info in this Answer does address the Question.

Answer 2

The 'Extra' Using index (not to be confused with Using index condition) in EXPLAIN means that all the columns mentioned in the SELECT are contained in the one INDEX being used.

In your query, all 3 columns are mentioned (* of SELECT *), but only one column is mentioned in the index (superText).

What I say applies to EXPLAIN and perhaps all forms of INDEX and all Engines, not just FULLTEXT with MyISAM.