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`)

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 ?


2 Answers 2


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.

  • I have now 30 rows in my table, and still the same issue.
    – Ant
    Commented May 8, 2012 at 7:25
  • I read many of your comments on FULLTEXT indexes yesterday, and found many of the links usefull.
    – Ant
    Commented May 8, 2012 at 7:26
  • Though valid, the info in this Answer does address the Question.
    – Rick James
    Commented Aug 30, 2018 at 23:34

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.