英文:
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_keys
是NULL
(而且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 |
In this case the database must look for fitting matches, and so needs to use the index to find corresponding rows faster
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论