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 ?

RolandoMySQLDBA · Answer 1 · 2020-12-04T15:05:36.903

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.

score 0 · Answer 2 · answered Aug 30 '18 at 23:33

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.

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

2 Answers2

Linked