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

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

Multiple MATCH-AGAINST clauses results in no FULLTEXT index being used

问题

我有以下两个表格：

  1. CREATE TABLE `lname` (
  2.   `lnameid` binary(16) NOT NULL,
  3.   `lid` binary(16) NOT NULL,
  4.   `name` varchar(200) NOT NULL,
  5.   `namerank` int(11) DEFAULT NULL,
  6.   `score` float DEFAULT NULL,
  7.   PRIMARY KEY (`lnameid`),
  8.   KEY `lid` (`lid`),
  9.   FULLTEXT KEY `name` (`name`),
  10.   CONSTRAINT `lname_ibfk_1` FOREIGN KEY (`lid`) REFERENCES `sl` (`lid`)
  11. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;
  13. CREATE TABLE `sl` (
  14.   `lid` binary(16) NOT NULL,
  15.   `sid` int(11) NOT NULL,
  16.   `laid` varchar(20) NOT NULL,
  17.   `definition` text DEFAULT NULL,
  18.   PRIMARY KEY (`lid`),
  19.   KEY `sid` (`sid`),
  20.   KEY `laid` (`laid`),
  21.   FULLTEXT KEY `definition` (`definition`),
  22.   CONSTRAINT `sl_ibfk_1` FOREIGN KEY (`sid`) REFERENCES `s` (`sid`),
  23.   CONSTRAINT `sl_ibfk_2` FOREIGN KEY (`laid`) REFERENCES `la` (`laid`)
  24. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;
  26. 以及以下查询：
  28. ```sql
  29. EXPLAIN
  30. SELECT MATCH(lname.name) AGAINST ('maillot' IN BOOLEAN MODE) AS nms,
  31.        MATCH(sl.definition) AGAINST ('maillot' IN BOOLEAN MODE) AS dms
  32. FROM   sl
  33. INNER JOIN lname ON lname.lid = sl.lid
  34. WHERE  MATCH(sl.definition) AGAINST ('maillot' IN BOOLEAN MODE) > 0
  35. 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:

  1. CREATE TABLE `lname` (
  2. `lnameid` binary(16) NOT NULL,
  3. `lid` binary(16) NOT NULL,
  4. `name` varchar(200) NOT NULL,
  5. `namerank` int(11) DEFAULT NULL,
  6. `score` float DEFAULT NULL,
  7. PRIMARY KEY (`lnameid`),
  8. KEY `lid` (`lid`),
  9. FULLTEXT KEY `name` (`name`),
  10. CONSTRAINT `lname_ibfk_1` FOREIGN KEY (`lid`) REFERENCES `sl` (`lid`)
  11. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci

and

  1. CREATE TABLE `sl` (
  2. `lid` binary(16) NOT NULL,
  3. `sid` int(11) NOT NULL,
  4. `laid` varchar(20) NOT NULL,
  5. `definition` text DEFAULT NULL,
  6. PRIMARY KEY (`lid`),
  7. KEY `sid` (`sid`),
  8. KEY `laid` (`laid`),
  9. FULLTEXT KEY `definition` (`definition`),
  10. CONSTRAINT `sl_ibfk_1` FOREIGN KEY (`sid`) REFERENCES `s` (`sid`),
  11. CONSTRAINT `sl_ibfk_2` FOREIGN KEY (`laid`) REFERENCES `la` (`laid`)
  12. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci

And the following query:

  1. EXPLAIN
  2. SELECT MATCH(lname.name) AGAINST ('maillot' IN BOOLEAN MODE) AS nms,
  3. MATCH(sl.definition) AGAINST ('maillot' IN BOOLEAN MODE) AS dms
  4. FROM   sl
  5. INNER JOIN lname ON lname.lid = sl.lid
  6. WHERE  MATCH(sl.definition) AGAINST ('maillot' IN BOOLEAN MODE) > 0
  7. OR     MATCH(lname.name) AGAINST ('maillot' IN BOOLEAN MODE) > 0;
  8. +------+-------------+-----------------+------+---------------+---------------+---------+----------------------------------+--------+-------------+
  9. | id   | select_type | table           | type | possible_keys | key           | key_len | ref                              | rows   | Extra       |
  10. +------+-------------+-----------------+------+---------------+---------------+---------+----------------------------------+--------+-------------+
  11. |    1 | SIMPLE      | sl              | ALL  | PRIMARY       | NULL          | NULL    | NULL                             | 130437 |             |
  12. |    1 | SIMPLE      | lname           | ref  | lid           | lid           | 16      | lid                              | 1      | Using where |
  13. +------+-------------+-----------------+------+---------------+---------------+---------+----------------------------------+--------+-------------+

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 强制查询执行完整表扫描，因此使用索引没有意义。"

  1. CREATE TABLE `sl` (
  2. `slid` binary(16) NOT NULL,
  3. `sid` int(11) NOT NULL,
  4. `laid` varchar(20) NOT NULL,
  5. `definition` text DEFAULT NULL,
  6. PRIMARY KEY (`slid`),
  7. KEY `sid` (`sid`),
  8. KEY `laid` (`laid`),
  9. FULLTEXT KEY `definition` (`definition`)#,
  10. #CONSTRAINT `sl_ibfk_1` FOREIGN KEY (`sid`) REFERENCES `s` (`sid`),
  11. #CONSTRAINT `sl_ibfk_2` FOREIGN KEY (`laid`) REFERENCES `la` (`laid`)
  12. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci
  1. CREATE TABLE `lname` (
  2. `lnameid` binary(16) NOT NULL,
  3. `lid` binary(16) NOT NULL,
  4. `name` varchar(200) NOT NULL,
  5. `namerank` int(11) DEFAULT NULL,
  6. `score` float DEFAULT NULL,
  7. PRIMARY KEY (`lnameid`),
  8. KEY `lid` (`lid`),
  9. FULLTEXT KEY `name` (`name`),
  10. CONSTRAINT `lname_ibfk_1` FOREIGN KEY (`lid`) REFERENCES `sl` (`slid`)
  11. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci
  1. EXPLAIN 
  2. SELECT MATCH(lname.name) AGAINST ('maillot' IN BOOLEAN MODE) AS nms,
  3. MATCH(sl.definition) AGAINST ('maillot' IN BOOLEAN MODE) AS dms
  4. FROM   sl
  5. INNER JOIN lname ON lname.lid = sl.slid
  6. WHERE  MATCH(sl.definition) AGAINST ('maillot' IN BOOLEAN MODE) > 0
  7. UNION
  8. SELECT MATCH(lname.name) AGAINST ('maillot' IN BOOLEAN MODE) AS nms,
  9. MATCH(sl.definition) AGAINST ('maillot' IN BOOLEAN MODE) AS dms
  10. FROM   sl
  11. INNER JOIN lname ON lname.lid = sl.slid
  12. 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

  1. CREATE TABLE `sl` (
  2. `slid` binary(16) NOT NULL,
  3. `sid` int(11) NOT NULL,
  4. `laid` varchar(20) NOT NULL,
  5. `definition` text DEFAULT NULL,
  6. PRIMARY KEY (`slid`),
  7. KEY `sid` (`sid`),
  8. KEY `laid` (`laid`),
  9. FULLTEXT KEY `definition` (`definition`)#,
  10. #CONSTRAINT `sl_ibfk_1` FOREIGN KEY (`sid`) REFERENCES `s` (`sid`),
  11. #CONSTRAINT `sl_ibfk_2` FOREIGN KEY (`laid`) REFERENCES `la` (`laid`)
  12. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci
  1. CREATE TABLE `lname` (
  2. `lnameid` binary(16) NOT NULL,
  3. `lid` binary(16) NOT NULL,
  4. `name` varchar(200) NOT NULL,
  5. `namerank` int(11) DEFAULT NULL,
  6. `score` float DEFAULT NULL,
  7. PRIMARY KEY (`lnameid`),
  8. KEY `lid` (`lid`),
  9. FULLTEXT KEY `name` (`name`),
  10. CONSTRAINT `lname_ibfk_1` FOREIGN KEY (`lid`) REFERENCES `sl` (`slid`)
  11. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci
  1. EXPLAIN 
  2. SELECT MATCH(lname.name) AGAINST (&#39;maillot&#39; IN BOOLEAN MODE) AS nms,
  3. MATCH(sl.definition) AGAINST (&#39;maillot&#39; IN BOOLEAN MODE) AS dms
  4. FROM   sl
  5. INNER JOIN lname ON lname.lid = sl.slid
  6. WHERE  MATCH(sl.definition) AGAINST (&#39;maillot&#39; IN BOOLEAN MODE) &gt; 0
  7. UNION
  8. SELECT MATCH(lname.name) AGAINST (&#39;maillot&#39; IN BOOLEAN MODE) AS nms,
  9. MATCH(sl.definition) AGAINST (&#39;maillot&#39; IN BOOLEAN MODE) AS dms
  10. FROM   sl
  11. INNER JOIN lname ON lname.lid = sl.slid
  12. 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
  • explain
  • full-text-search
  • mariadb
  • sql
匿名

发表评论

匿名网友

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

确定