How to fix mysql full-text index is too slow-CodePudding

I moved from the local environment to the rds environment of aws.

innodb_ft_enable_stopword=0
ngram_token_size=1

I have the above two settings.

CREATE TABLE `keywordTable` (
  `id` int NOT NULL AUTO_INCREMENT,
  `createdAt` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  `updatedAt` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  `userId` int DEFAULT NULL,
  `keyword` text NOT NULL,
  PRIMARY KEY (`id`),
  KEY `FK_2dd7820158cdf3dsasf` (`userId`),
  FULLTEXT KEY `IDX_e89f81c42dswdfdf` (`keyword`) /*!50100 WITH PARSER `ngram` */ ,
  CONSTRAINT `FK_2dd7820158c24303eb9f6618b9f` FOREIGN KEY (`userId`) REFERENCES `user` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=947181 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

The table was created with the query above.

SELECT keyword from keywordTable where MATCH(keyword) AGAINST(' aa' in boolean mode);

The query above takes almost a minute. Even the explain takes more than 4 seconds, and the result is as follows.

left	center	right
One	Two	Three

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	keywordTable		fulltext	IDX_e89f81c42dswdfdf	IDX_e89f81c42dswdfdf	0	const	1	100.00	Using where; Ft_hints: no_ranking

And I also tried optimize table keyword Table and REPAIR TABLE keyword Table QUICK but same result. what more do i have to do?

CodePudding user response：

Searching for a single character...

SELECT keyword from keywordTable WHERE keyword LIKE '%a%';

is probably the fastest. It will have to scan the entire table, and cannot use any type of index.

Doing the equivalent with "ngram" is probably slower -- it must scan a chunk of the index, then reach (randomly) into the data to get the actual keyword.

Consider ngram_token_size=2 (and rebuild the index) and either do the LIKE above (for 1-char search) or do the FULLTEXT that you have (for 2 ).

Then compare that (ngram=2) with the equivalent LIKE:

SELECT keyword from keywordTable WHERE keyword LIKE '