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

Question

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.

Answer 1

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 ('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