多个MATCH-AGAINST子句导致未使用任何FULLTEXT索引。

huangapple go评论68阅读模式
英文:

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

fiddle

英文:

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 (&#39;maillot&#39; IN BOOLEAN MODE) AS nms,
MATCH(sl.definition) AGAINST (&#39;maillot&#39; IN BOOLEAN MODE) AS dms
FROM   sl
INNER JOIN lname ON lname.lid = sl.slid
WHERE  MATCH(sl.definition) AGAINST (&#39;maillot&#39; IN BOOLEAN MODE) &gt; 0
UNION
SELECT MATCH(lname.name) AGAINST (&#39;maillot&#39; IN BOOLEAN MODE) AS nms,
MATCH(sl.definition) AGAINST (&#39;maillot&#39; IN BOOLEAN MODE) AS dms
FROM   sl
INNER JOIN lname ON lname.lid = sl.slid
WHERE MATCH(lname.name) AGAINST (&#39;maillot&#39; IN BOOLEAN MODE) &gt; 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 &lt;union1,2> ALL null null null null null

fiddle

huangapple
  • 本文由 发表于 2023年6月19日 21:50:38
  • 转载请务必保留本文链接:https://go.coder-hub.com/76507286.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定