如何让mysql的联表查询走全文索引呢？

Question

SELECT * FROM data_content_193 LEFT JOIN data_content_41 ON (data_content_193.编号 = data_content_41.代号) WHERE MATCH (data_content_193.姓名, data_content_193.学校名称, data_content_193.职务, data_content_193.住址, data_content_41.公司地址) AGAINST ('+"北京" +"王"' IN BOOLEAN MODE);

以上sql使用了联表查询，并希望使用全文索引提高搜索速度，为此，我已经分别在两个表中建立了以下两个全文索引

create fulltext index search_index_fulltext6 on data_content_193(姓名,学校名称,职务,住址) with parser ngram;
create fulltext index search_index_fulltext7 on data_content_41(公司地址) with parser ngram;

但是通过EXPLAIN发现，该sql并没有走任何一个全文索引，如何做才可以让该sql的联表查询走全文索引呢？

mysql> EXPLAIN SELECT * FROM data_content_193 LEFT JOIN data_content_41 ON (data_content_193.编号 = data_content_41.代号) WHERE MATCH (data_content_193.姓名, data_content_193.学校名称, data_content_193.职务, data_content_193.住址, data_content_41.公司地址) AGAINST ('+"北京" +"王"' IN BOOLEAN MODE);
+----+-------------+------------------+------------+------+---------------------------------+---------------------------------+---------+-----------------------------+--------+----------+-------------+
| id | select_type | table            | partitions | type | possible_keys                   | key                             | key_len | ref                         | rows   | filtered | Extra       |
+----+-------------+------------------+------------+------+---------------------------------+---------------------------------+---------+-----------------------------+--------+----------+-------------+
|  1 | SIMPLE      | data_content_193 | NULL       | ALL  | NULL                            | NULL                            | NULL    | NULL                        | 544460 |   100.00 | NULL        |
|  1 | SIMPLE      | data_content_41  | NULL       | ref  | idx_data_content_41_代号        | idx_data_content_41_db识别码    | 183     | dmm.data_content_193.番号   |      1 |    11.11 | Using where |
+----+-------------+------------------+------------+------+---------------------------------+---------------------------------+---------+-----------------------------+--------+----------+-------------+
2 rows in set, 1 warning (0.27 sec)

Answer 1

该回答引用自ChatGPT

首先需要注意的是，全文索引只能应用于MATCH AGAINST语句中的查询条件，而不能直接应用于联表查询的ON条件。因此，你需要将联表查询和全文索引的查询条件分开来写。
一种可能的解决方案是，先通过全文索引查询出符合条件的数据编号，再以此作为子查询的条件进行联表查询。具体的SQL语句可以类似如下：

SELECT *
FROM data_content_193
LEFT JOIN data_content_41 ON (data_content_193.编号 = data_content_41.代号)
WHERE data_content_193.编号 IN (
  SELECT data_content_193.编号
  FROM data_content_193
  WHERE MATCH (data_content_193.姓名, data_content_193.学校名称, data_content_193.职务, data_content_193.住址) AGAINST ('+"北京" +"王"' IN BOOLEAN MODE)
)
OR data_content_41.代号 IN (
  SELECT data_content_41.代号
  FROM data_content_41
  WHERE MATCH (data_content_41.公司地址) AGAINST ('+"北京" +"王"' IN BOOLEAN MODE)
)

在这个查询中，先分别使用全文索引查询出符合条件的数据编号，然后在联表查询中使用这些编号作为过滤条件，只返回符合条件的数据。这样就可以利用全文索引提高查询效率了。
需要注意的是，这种做法可能会有一些性能问题，因为需要进行多次子查询。如果数据量比较大，可能会导致查询速度变慢。因此，你需要根据具体情况来权衡利弊，选择适合自己的解决方案。