MySQL fulltext query is very slow | What I'm doing wrong?

Question

I have a very simple table with 100,000 rows.

CREATE TABLE test_database.products (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `city_id` int  NOT NULL,
  `market_id` int NOT NULL,
  `product_id` int NOT NULL,
  `search` varchar(450) COLLATE utf8mb4_unicode_ci NOT NULL,
  `quantity` int NOT NULL,
  `price` varchar(15) COLLATE utf8mb4_unicode_ci NOT NULL,
   PRIMARY KEY (`id`)
)
ENGINE=InnoDB
DEFAULT CHARSET=utf8
COLLATE=utf8_general_ci;

I added fulltext:

ALTER TABLE products ADD fulltext (search);

And when I search:

    SELECT 
        id, market_id, product_id, quantity, price,
        MATCH(pr.search) AGAINST('dolor* qui*' IN BOOLEAN MODE) as srv 
    from 
        products pr 
    where 
        pr.city_id = 1 and
        MATCH(pr.search) AGAINST('dolor* qui*' IN BOOLEAN MODE) 
    ORDER by
        srv DESC;

It takes ~ 2500 ms.

Explain select ...

id|select_type|table|partitions|type    |possible_keys|key   |key_len|ref  |rows|filtered|Extra                        |
--|-----------|-----|----------|--------|-------------|------|-------|-----|----|--------|-----------------------------|
 1|SIMPLE     |pr   |          |fulltext|search       |search|0      |const|   1|    10.0|Using where; Ft_hints: sorted|

MySQL version:

 Ver 8.0.21 for Win64 on x86_64

What I'm doing wrong?

Answer 1

Give this formulation a try:

SELECT ..., x.relevance
    FROM (
        SELECT  id,
                MATCH(...) AGAINST(...) AS relevance
            FROM products 
            WHERE MATCH(...) AGAINST(...)
         ) AS x
    JOIN products AS pr  USING(id)
    WHERE pr.city_id = 1
    ORDER BY x.relevance DESC

Answer 2

You are trying to run match twice, also you have to specify which score you want to have, which means how good the match should be.

Se if a score bigger as 0.5 helps else try a lower number

Also you should look at https://dev.mysql.com/doc/refman/8.0/en/fulltext-boolean.html it explains a lot about Boolean seraches

SELECT 
    id, market_id, product_id, quantity, price,
    MATCH(pr.search) AGAINST('dolor* qui*' IN BOOLEAN MODE) as srv 
from 
    products pr 
where 
    pr.city_id = 1 
HAVING srv > 0.5
ORDER by
    srv DESC
;