英文:
Multiple MATCH-AGAINST clauses results in no FULLTEXT index being used
问题
我有以下两个表格:
CREATE TABLE `lname` (
`lnameid` binary(16) NOT NULL,
`lid` binary(16) NOT NULL,
`name` varchar(200) NOT NULL,
`namerank` int(11) DEFAULT NULL,
`score` float DEFAULT NULL,
PRIMARY KEY (`lnameid`),
KEY `lid` (`lid`),
FULLTEXT KEY `name` (`name`),
CONSTRAINT `lname_ibfk_1` FOREIGN KEY (`lid`) REFERENCES `sl` (`lid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;
CREATE TABLE `sl` (
`lid` binary(16) NOT NULL,
`sid` int(11) NOT NULL,
`laid` varchar(20) NOT NULL,
`definition` text DEFAULT NULL,
PRIMARY KEY (`lid`),
KEY `sid` (`sid`),
KEY `laid` (`laid`),
FULLTEXT KEY `definition` (`definition`),
CONSTRAINT `sl_ibfk_1` FOREIGN KEY (`sid`) REFERENCES `s` (`sid`),
CONSTRAINT `sl_ibfk_2` FOREIGN KEY (`laid`) REFERENCES `la` (`laid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;
以及以下查询:
```sql
EXPLAIN
SELECT MATCH(lname.name) AGAINST ('maillot' IN BOOLEAN MODE) AS nms,
MATCH(sl.definition) AGAINST ('maillot' IN BOOLEAN MODE) AS dms
FROM sl
INNER JOIN lname ON lname.lid = sl.lid
WHERE MATCH(sl.definition) AGAINST ('maillot' IN BOOLEAN MODE) > 0
OR MATCH(lname.name) AGAINST ('maillot' IN BOOLEAN MODE) > 0;
正如您所见,两个FULLTEXT
索引都没有被使用。
如果我只应用一个MATCH
(无论是第一个还是第二个,并保持JOIN
子句不变),相应的FULLTEXT
索引就会被使用。
我不明白为什么多个MATCH
子句突然导致MariaDB不使用这些索引。
我使用的是MariaDB 10.5.19版本。
英文:
I have the following two tables:
CREATE TABLE `lname` (
`lnameid` binary(16) NOT NULL,
`lid` binary(16) NOT NULL,
`name` varchar(200) NOT NULL,
`namerank` int(11) DEFAULT NULL,
`score` float DEFAULT NULL,
PRIMARY KEY (`lnameid`),
KEY `lid` (`lid`),
FULLTEXT KEY `name` (`name`),
CONSTRAINT `lname_ibfk_1` FOREIGN KEY (`lid`) REFERENCES `sl` (`lid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci
and
CREATE TABLE `sl` (
`lid` binary(16) NOT NULL,
`sid` int(11) NOT NULL,
`laid` varchar(20) NOT NULL,
`definition` text DEFAULT NULL,
PRIMARY KEY (`lid`),
KEY `sid` (`sid`),
KEY `laid` (`laid`),
FULLTEXT KEY `definition` (`definition`),
CONSTRAINT `sl_ibfk_1` FOREIGN KEY (`sid`) REFERENCES `s` (`sid`),
CONSTRAINT `sl_ibfk_2` FOREIGN KEY (`laid`) REFERENCES `la` (`laid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci
And the following query:
EXPLAIN
SELECT MATCH(lname.name) AGAINST ('maillot' IN BOOLEAN MODE) AS nms,
MATCH(sl.definition) AGAINST ('maillot' IN BOOLEAN MODE) AS dms
FROM sl
INNER JOIN lname ON lname.lid = sl.lid
WHERE MATCH(sl.definition) AGAINST ('maillot' IN BOOLEAN MODE) > 0
OR MATCH(lname.name) AGAINST ('maillot' IN BOOLEAN MODE) > 0;
+------+-------------+-----------------+------+---------------+---------------+---------+----------------------------------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-----------------+------+---------------+---------------+---------+----------------------------------+--------+-------------+
| 1 | SIMPLE | sl | ALL | PRIMARY | NULL | NULL | NULL | 130437 | |
| 1 | SIMPLE | lname | ref | lid | lid | 16 | lid | 1 | Using where |
+------+-------------+-----------------+------+---------------+---------------+---------+----------------------------------+--------+-------------+
As you can see, none of the two FULLTEXT
indexes are being used.
If I only apply one MATCH
(either the first or the second one, and keeping the JOIN
clause as-is), the corresponding FULLTEXT
index is used.
I don't understand why multiple MATCH
clauses suddenly cause MariaDB to stop using the indexes
I am using MariaDB 10.5.19.
答案1
得分: 1
以下是您要翻译的内容:
"The OR forces the query ti make a full table scan so a use of the indexdes makes no sense."
将其更改为:
"OR 强制查询执行完整表扫描,因此使用索引没有意义。"
CREATE TABLE `sl` (
`slid` binary(16) NOT NULL,
`sid` int(11) NOT NULL,
`laid` varchar(20) NOT NULL,
`definition` text DEFAULT NULL,
PRIMARY KEY (`slid`),
KEY `sid` (`sid`),
KEY `laid` (`laid`),
FULLTEXT KEY `definition` (`definition`)#,
#CONSTRAINT `sl_ibfk_1` FOREIGN KEY (`sid`) REFERENCES `s` (`sid`),
#CONSTRAINT `sl_ibfk_2` FOREIGN KEY (`laid`) REFERENCES `la` (`laid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci
CREATE TABLE `lname` (
`lnameid` binary(16) NOT NULL,
`lid` binary(16) NOT NULL,
`name` varchar(200) NOT NULL,
`namerank` int(11) DEFAULT NULL,
`score` float DEFAULT NULL,
PRIMARY KEY (`lnameid`),
KEY `lid` (`lid`),
FULLTEXT KEY `name` (`name`),
CONSTRAINT `lname_ibfk_1` FOREIGN KEY (`lid`) REFERENCES `sl` (`slid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci
EXPLAIN
SELECT MATCH(lname.name) AGAINST ('maillot' IN BOOLEAN MODE) AS nms,
MATCH(sl.definition) AGAINST ('maillot' IN BOOLEAN MODE) AS dms
FROM sl
INNER JOIN lname ON lname.lid = sl.slid
WHERE MATCH(sl.definition) AGAINST ('maillot' IN BOOLEAN MODE) > 0
UNION
SELECT MATCH(lname.name) AGAINST ('maillot' IN BOOLEAN MODE) AS nms,
MATCH(sl.definition) AGAINST ('maillot' IN BOOLEAN MODE) AS dms
FROM sl
INNER JOIN lname ON lname.lid = sl.slid
WHERE MATCH(lname.name) AGAINST ('maillot' IN BOOLEAN MODE) > 0;
id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
---|---|---|---|---|---|---|---|---|---|
1 | PRIMARY | sl | fulltext | PRIMARY,definition | definition | 0 | 1 | Using where | |
1 | PRIMARY | lname | ALL | lid | null | null | null | 1 | Using where |
2 | UNION | sl | ALL | PRIMARY | null | null | null | 1 | |
2 | UNION | lname | fulltext | lid,name | name | 0 | 1 | Using where | |
null | UNION RESULT | <union1,2> | ALL | null | null | null | null | null |
英文:
The OR forces the query ti make a full table scan so a use of the indexdes makes no sense.
changing it to
CREATE TABLE `sl` (
`slid` binary(16) NOT NULL,
`sid` int(11) NOT NULL,
`laid` varchar(20) NOT NULL,
`definition` text DEFAULT NULL,
PRIMARY KEY (`slid`),
KEY `sid` (`sid`),
KEY `laid` (`laid`),
FULLTEXT KEY `definition` (`definition`)#,
#CONSTRAINT `sl_ibfk_1` FOREIGN KEY (`sid`) REFERENCES `s` (`sid`),
#CONSTRAINT `sl_ibfk_2` FOREIGN KEY (`laid`) REFERENCES `la` (`laid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci
CREATE TABLE `lname` (
`lnameid` binary(16) NOT NULL,
`lid` binary(16) NOT NULL,
`name` varchar(200) NOT NULL,
`namerank` int(11) DEFAULT NULL,
`score` float DEFAULT NULL,
PRIMARY KEY (`lnameid`),
KEY `lid` (`lid`),
FULLTEXT KEY `name` (`name`),
CONSTRAINT `lname_ibfk_1` FOREIGN KEY (`lid`) REFERENCES `sl` (`slid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci
EXPLAIN
SELECT MATCH(lname.name) AGAINST ('maillot' IN BOOLEAN MODE) AS nms,
MATCH(sl.definition) AGAINST ('maillot' IN BOOLEAN MODE) AS dms
FROM sl
INNER JOIN lname ON lname.lid = sl.slid
WHERE MATCH(sl.definition) AGAINST ('maillot' IN BOOLEAN MODE) > 0
UNION
SELECT MATCH(lname.name) AGAINST ('maillot' IN BOOLEAN MODE) AS nms,
MATCH(sl.definition) AGAINST ('maillot' IN BOOLEAN MODE) AS dms
FROM sl
INNER JOIN lname ON lname.lid = sl.slid
WHERE MATCH(lname.name) AGAINST ('maillot' IN BOOLEAN MODE) > 0;
id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
---|---|---|---|---|---|---|---|---|---|
1 | PRIMARY | sl | fulltext | PRIMARY,definition | definition | 0 | 1 | Using where | |
1 | PRIMARY | lname | ALL | lid | null | null | null | 1 | Using where |
2 | UNION | sl | ALL | PRIMARY | null | null | null | 1 | |
2 | UNION | lname | fulltext | lid,name | name | 0 | 1 | Using where | |
null | UNION RESULT | <union1,2> | ALL | null | null | null | null | null |
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论