John Pangilinan John Pangilinan - 13 days ago 5
MySQL Question

MySQL - How to know if query is using FTS index

As the title says, how would you know if a query uses FTS index or not?

normally it shows

using index condition
.

my query was:

SELECT *
FROM news
WHERE MATCH(news_title,news_keywords) AGAINST ('news')
AND news_date_created BETWEEN '2016-01-01' AND '2016-12-31'


and executed
EXPLAIN
and gave me:

+---+---+---+---+---+---+---+---+---+---+---+---+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+---+---+---+---+---+---+---+---+---+---+---+---+
| 1 | SIMPLE | news | NULL | fulltext | news_date_idx,news_title | news_title | 0 | const | 1 | 100.00 | Using where; Ft_hints: sorted |
+---+---+---+---+---+---+---+---+---+---+---+---+


I don't know if it's using the index or not. It just says it is using
where and ft_hints
.

Answer

The answer is in the explain result's key column, that lists the index(es) used. In this particular case news_title index was used. If this is your fulltext index, then it was used.

Also, note, that apart from fulltext search in Boolean mode on myiasm tables, all searches require a matching fulltext index to be present:

The MATCH() column list must match exactly the column list in some FULLTEXT index definition for the table, unless this MATCH() is IN BOOLEAN MODE on a MyISAM table. For MyISAM tables, boolean-mode searches can be done on nonindexed columns, although they are likely to be slow.