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

Question

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.

Answer 1

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