|
From SO.
I have an issue with fulltext search when migrating from MySQL 5.7 to MariaDB 10.6.
In MariaDB it is a lot slower - taking up to 10 seconds when the same query takes 0.15 in MySQL.
The query is the following:
SELECT `products`.*,
|
MATCH (`products_search`.`distid`, `products_search`.`distCode`, `products_search`.`description`,
|
`products_search`.`namedist`,`products_search`.`name`, `products_search`.`vendor`,
|
`products_search`.`model`,`products_search`.`shortdesc`, `products_search`.`name_emag` )
|
AGAINST ( ' memory* card*' IN BOOLEAN MODE ) AS `score`
|
FROM `products_search`
|
INNER JOIN `products` ON `products`.`id` = `products_search`.`id`
|
WHERE MATCH (`products_search`.`distid`, `products_search`.`distCode`, `products_search`.`description`,
|
`products_search`.`namedist`, `products_search`.`name`, `products_search`.`vendor`,
|
`products_search`.`model`, `products_search`.`shortdesc`, `products_search`.`name_emag` )
|
AGAINST ( ' memory* card*' IN BOOLEAN MODE) > 0
|
AND `products`.`instock` > 0
|
AND `products`.`delivery` = 0
|
AND `products`.`enabled`=1
|
ORDER BY score DESC LIMIT 20
|
|
EXPLAIN on MySQL says
|
|
Using where; Ft_hints: sorted, rank > 0
|
Using where
|
|
EXPLAIN on MariaDB gives:
|
|
Using where; Using temporary; Using filesort
|
Using where
|
MariaDB> SHOW CREATE TABLE `products_search`:
|
|
|
CREATE TABLE `products_search` (
|
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
|
`distid` varchar(45) NOT NULL,
|
`distCode` varchar(255) NOT NULL,
|
`distributor` smallint(6) NOT NULL,
|
`ean` varchar(13) DEFAULT '',
|
`description` text NOT NULL,
|
`namedist` varchar(255) NOT NULL,
|
`name` varchar(100) NOT NULL DEFAULT '',
|
`name_emag` varchar(255) NOT NULL DEFAULT '' COMMENT 'name for emag',
|
`model` varchar(30) NOT NULL DEFAULT '' COMMENT 'модел на продукта',
|
`shortdesc` varchar(255) NOT NULL DEFAULT '',
|
`instock` tinyint(1) NOT NULL,
|
`delivery` smallint(5) unsigned NOT NULL DEFAULT 0 COMMENT '0-налично, 100-на път, 1000-с поръчка',
|
`limitedQty` tinyint(1) NOT NULL DEFAULT 0,
|
`category` smallint(6) NOT NULL,
|
`vendor` varchar(30) NOT NULL,
|
PRIMARY KEY (`id`),
|
KEY `category` (`category`),
|
KEY `distCode` (`distCode`),
|
KEY `vendor_index` (`vendor`),
|
KEY `name_index` (`name`) COMMENT 'name index',
|
KEY `namedist-index` (`namedist`),
|
KEY `ean` (`ean`),
|
KEY `model_btree` (`model`) USING BTREE,
|
FULLTEXT KEY `fulltext_multicol` (`name`,`namedist`,`distCode`,`distid`,`description`,`model`,`shortdesc`,`vendor`,`name_emag`)
|
) ENGINE=InnoDB AUTO_INCREMENT=395467 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci
|
MariaDB ANALYZE
MySQL 5.7 EXPLAIN EXTENDED
MariaDB hasn't implemented WL#7123 Additional query optimization for Fulltext Search
https://github.com/mysql/mysql-server/commit/f7bdd3491351
|