MySQL - 如何知道查询是否使用 FTS 索引

Question

如标题所述，您如何知道查询是否使用 FTS 索引？

通常显示using index condition。

我的查询是:

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

并执行 EXPLAIN 并给了我:

+----+-------------+-------+------------+----------+--------------------------+------------+---------+-------+------+----------+-------------------------------+
| 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 |
+----+-------------+-------+------------+----------+--------------------------+------------+---------+-------+------+----------+-------------------------------+

不知道是不是在用索引。它只是说它正在使用 where 和 ft_hints。

Answer 1

答案在解释结果的 key 列中，该列列出了使用的索引。在这种特殊情况下，使用了 news_title 索引。如果这是您的全文索引，那么它已被使用。

另外，请注意，除了 fulltext search in Boolean mode on myiasm tables ，所有搜索都需要匹配的全文索引:

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.