ERROR 188 (HY000): FTS query exceeds result cache limit mysql

Question

I have full text index on text column of my table with around 11 million rows.

Table structure :

CREATE TABLE `review` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `comments` text COLLATE utf8mb4_unicode_ci,
  `title` varchar(1000) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `reviewer_id` (`reviewer_id`),
  FULLTEXT KEY `comments` (`comments`)
) ENGINE=InnoDB AUTO_INCREMENT=273001866 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=COMPRESSED;

I tried search like :

SELECT 
    id
FROM
    review
WHERE  MATCH (comments) AGAINST ('"This is review is only for Campus tours and not for University itself as no one can write a review on University"' IN BOOLEAN MODE)

This is throwing following error :

ERROR 188 (HY000): FTS query exceeds result cache limit

Google says this was a bug which has been fixed by mysql in 5.7. I am using 5.7.19. Any idea how to resolve this. Copy pasting ft variables:

mysql> show global variables like 'innodb_ft%';
+---------------------------------+--------------------+
| Variable_name                   | Value              |
+---------------------------------+--------------------+
| innodb_ft_aux_table             |                    |
| innodb_ft_cache_size            | 8000000            |
| innodb_ft_enable_diag_print     | OFF                |
| innodb_ft_enable_stopword       | ON                 |
| innodb_ft_max_token_size        | 84                 |
| innodb_ft_min_token_size        | 3                  |
| innodb_ft_num_word_optimize     | 2000               |
| innodb_ft_result_cache_limit    | 2000000000         |
| innodb_ft_server_stopword_table | local/my_stopwords |
| innodb_ft_sort_pll_degree       | 2                  |
| innodb_ft_total_cache_size      | 640000000          |
| innodb_ft_user_stopword_table   |                    |
+---------------------------------+--------------------+
12 rows in set (0.00 sec)

Clovah

Answer 1

I have encountered this problem after adding a full-text index to a text column in a huge table (10 mln rows). Previously I solved this by restarting the server, but now I can't restart it because it's performing some computations.

Solved this by adjusting this setting (2 times more than default):

SET GLOBAL innodb_ft_result_cache_limit = 4000000000;

Valeriavalerian

Answer 2

From what I noticed on my server, this error can also be the result of many repeated search queries that are very complex and with many bolean operators.

E.g if you run a search engine, you might be getting spammed with long complex queries.

I would have a look at recent queries that might have caused this, before increasing the limits.

Everyway