MariaDB非常简单的MATCH-AGAINST查询不使用FULLTEXT索引?

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

MariaDB very simple MATCH-AGAINST query not using FULLTEXT index?

问题

我有以下通过SHOW CREATE TABLE显示的表格:

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

这个表格大约有230,000行。所有行的name列都有非NULL值。

我有以下查询:

EXPLAIN SELECT MATCH(lname.name) AGAINST ('maillot' IN BOOLEAN MODE) AS nms FROM lname;
+------+-------------+-----------------+------+---------------+------+---------+------+--------+-------+
| id   | select_type | table           | type | possible_keys | key  | key_len | ref  | rows   | Extra |
+------+-------------+-----------------+------+---------------+------+---------+------+--------+-------+
|    1 | SIMPLE      | lname           | ALL  | NULL          | NULL | NULL    | NULL | 228370 |       |
+------+-------------+-----------------+------+---------------+------+---------+------+--------+-------+

我的问题是:我看到possible_keysNULL(而且Extra为空),这是否意味着我的FULLTEXT索引没有被使用?

这个查询似乎执行得很慢。

我在Linux上使用的是MariaDB 10.5.19。

英文:

I have the following table as displayed by SHOW CREATE TABLE:

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

This table has about 230,000 rows. All rows have non-NULL values for the name column.

I have the following query:

EXPLAIN SELECT MATCH(lname.name) AGAINST ('maillot' IN BOOLEAN MODE) AS nms FROM lname;
+------+-------------+-----------------+------+---------------+------+---------+------+--------+-------+
| id   | select_type | table           | type | possible_keys | key  | key_len | ref  | rows   | Extra |
+------+-------------+-----------------+------+---------------+------+---------+------+--------+-------+
|    1 | SIMPLE      | lname           | ALL  | NULL          | NULL | NULL    | NULL | 228370 |       |
+------+-------------+-----------------+------+---------------+------+---------+------+--------+-------+

My question is: I see possible_keys is NULL (and Extra is empty), does it mean that my FULLTEXT index is somehow not being used?

The query seems slow to execute.

I'm using MariaDB 10.5.19 on Linux.

答案1

得分: 0

你正在计算所有行的匹配系数,因此不需要索引,但当你将它更改为

CREATE TABLE `lname` (
  `lnameid` binary(16) NOT NULL,
  `lid` binary(16) NOT NULL,
  `name` TEXT NOT NULL,
  `namerank` int(11) DEFAULT NULL,
  `score` float DEFAULT NULL,
  PRIMARY KEY (`lnameid`),
  KEY `lid` (`lid`),
  FULLTEXT KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci
INSERT INTO lname VALUES (1, 1, 'a', 1, 1)
EXPLAIN SELECT MATCH(name) AGAINST ('maillot' IN BOOLEAN MODE) AS nms FROM lname
  WHERE MATCH(name) AGAINST ('maillot' IN BOOLEAN MODE) > 1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE lname fulltext name name 0 1 Using where

在这种情况下,数据库必须寻找匹配项,因此需要使用索引以更快地找到相应的行。

英文:

What you are doing is calculating a match coefficient for all rows, so no index is needed, but when you change it to

CREATE TABLE `lname` (
  `lnameid` binary(16) NOT NULL,
  `lid` binary(16) NOT NULL,
  `name` TEXT 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

INSERT INTO  lname vALUES (1,1,'a',1,1)
EXPLAIN SELECT MATCH(name) AGAINST ('maillot' IN BOOLEAN MODE) AS nms FROM lname
  WHERE MATCH(name) AGAINST ('maillot' IN BOOLEAN MODE) > 1;

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE lname fulltext name name 0 1 Using where

fiddle

In this case the database must look for fitting matches, and so needs to use the index to find corresponding rows faster

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

发表评论

匿名网友

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

确定