full text search – MySQL fulltext query is very slow | What I’m doing wrong?


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.

What I’m doing wrong?