Mysql Fulltext Index在多个表中使用AND条件时速度很慢

Question

我有两个大表(55M行)，结构如下:

CREATE TABLE `chapters` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`toc` varchar(5000) COLLATE utf8mb4_unicode_ci NOT NULL,
`author` varchar(5000) COLLATE utf8mb4_unicode_ci NOT NULL,
`ari_id` bigint(20) NOT NULL,
PRIMARY KEY (`id`),
KEY `ari_id` (`ari_id`),
FULLTEXT KEY `toc` (`toc`),
FULLTEXT KEY `author` (`author`)
) ENGINE=InnoDB AUTO_INCREMENT=52251463 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
CREATE TABLE `books` (
`ID` int(15) unsigned NOT NULL AUTO_INCREMENT,
`Title` varchar(2000) COLLATE utf8mb4_unicode_ci DEFAULT '',
`Author` varchar(2000) COLLATE utf8mb4_unicode_ci DEFAULT '',
`isOpenAccess` tinyint(1) NOT NULL,
`ari_id` bigint(20) NOT NULL,
PRIMARY KEY (`ID`),
UNIQUE KEY `ari_id` (`ari_id`),
FULLTEXT KEY `Title` (`Title`),
FULLTEXT KEY `Author` (`Author`),
) ENGINE=InnoDB AUTO_INCREMENT=2627161 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

我正在使用以下查询进行搜索:

SELECT b.ari_id, b.Title, b.Author, t.toc, t.author
FROM books b
INNER JOIN chapters t
ON b.ari_id = t.ari_id
WHERE MATCH(t.toc) AGAINST('power*' IN BOOLEAN MODE)
AND b.isOpenaccess = 1 
LIMIT 300

将在大约12秒内返回结果。我有没有可能加快响应时间?

第二，当我尝试使用"one_answers"从两个全文索引中搜索时接线员，回复时间很长(146秒)。我正在运行的查询如下:

SELECT toc, author
FROM tocs
WHERE MATCH(toc) AGAINST('high*' IN BOOLEAN MODE)
AND MATCH(author) AGAINST('max*' IN BOOLEAN MODE) 
LIMIT 300

Answer 1

在books中，ari_id可以是PRIMARY KEY，可以去掉id。(这可能对性能有帮助，也可能没有)

MySQL喜欢先运行WHERE的FULLTEXT部分，然后运行AND和其他测试。

:

WHERE MATCH(toc) AGAINST('high*' IN BOOLEAN MODE)
AND MATCH(author) AGAINST('max*' IN BOOLEAN MODE) 

可以通过FULLTEXT(toc, author)和

加速

WHERE MATCH(toc, author) AGAINST('high* max*' IN BOOLEAN MODE)

但是它会找到额外的行。(例如，toc有high和max，但author两者都没有)

当对两个已连接表中的每一个执行FT查询时，类似的技巧是不可能的。

OTOH，通过拥有包含所有列的第三个表，加上ari_id，将允许您将测试组合到单个MATCH中。然后进一步细化。