Bug #86036 | InnoDB FULLTEXT index has too strict innodb_ft_result_cache_limit max limit | ||
---|---|---|---|
Submitted: | 21 Apr 2017 10:08 | Modified: | 23 Apr 2017 0:52 |
Reporter: | Valeriy Kravchuk | Email Updates: | |
Status: | Verified | Impact on me: | |
Category: | MySQL Server: InnoDB storage engine | Severity: | S3 (Non-critical) |
Version: | 5.7/8.0 | OS: | Any |
Assigned to: | CPU Architecture: | Any | |
Tags: | fulltext, innodb, innodb_ft_result_cache_limit, Optimizer |
[21 Apr 2017 10:08]
Valeriy Kravchuk
[22 Apr 2017 21:49]
MySQL Verification Team
c:\dbs>c:\dbs\5.7\bin\mysql -uroot --port=3570 -p --local-infile --prompt="mysql 5.7 > " Enter password: ****** Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.7.19 Source distribution 2017-apr-19 Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql 5.7 > CREATE DATABASE test; Query OK, 1 row affected (0.00 sec) mysql 5.7 > USE test Database changed mysql 5.7 > set global innodb_flush_log_at_trx_commit=0; Query OK, 0 rows affected (0.00 sec) mysql 5.7 > create table ft(id int auto_increment primary key, c1 varchar(255), fulltext key k(c1)) engine=InnoDB; Query OK, 0 rows affected (2.11 sec) mysql 5.7 > insert into ft(c1) values (repeat('order', 50)); Query OK, 1 row affected (0.00 sec) mysql 5.7 > insert into ft(c1) values (repeat('order ', 40)); Query OK, 1 row affected (0.00 sec) mysql 5.7 > insert into ft(c1) values (repeat('nothin', 40)); Query OK, 1 row affected (0.00 sec) mysql 5.7 > insert into ft(c1) select c1 from ft; Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql 5.7 > insert into ft(c1) select c1 from ft; Query OK, 6 rows affected (0.00 sec) Records: 6 Duplicates: 0 Warnings: 0 mysql 5.7 > insert into ft(c1) select c1 from ft; Query OK, 12 rows affected (0.00 sec) Records: 12 Duplicates: 0 Warnings: 0 mysql 5.7 > insert into ft(c1) select c1 from ft; Query OK, 24 rows affected (0.00 sec) Records: 24 Duplicates: 0 Warnings: 0 mysql 5.7 > insert into ft(c1) select c1 from ft; Query OK, 48 rows affected (0.10 sec) Records: 48 Duplicates: 0 Warnings: 0 mysql 5.7 > insert into ft(c1) select c1 from ft; Query OK, 96 rows affected (0.06 sec) Records: 96 Duplicates: 0 Warnings: 0 mysql 5.7 > insert into ft(c1) select c1 from ft; Query OK, 192 rows affected (0.27 sec) Records: 192 Duplicates: 0 Warnings: 0 mysql 5.7 > insert into ft(c1) select c1 from ft; Query OK, 384 rows affected (0.17 sec) Records: 384 Duplicates: 0 Warnings: 0 mysql 5.7 > insert into ft(c1) select c1 from ft; Query OK, 768 rows affected (0.37 sec) Records: 768 Duplicates: 0 Warnings: 0 mysql 5.7 > insert into ft(c1) select c1 from ft; Query OK, 1536 rows affected (0.57 sec) Records: 1536 Duplicates: 0 Warnings: 0 mysql 5.7 > insert into ft(c1) select c1 from ft; Query OK, 3072 rows affected (0.07 sec) Records: 3072 Duplicates: 0 Warnings: 0 mysql 5.7 > insert into ft(c1) select c1 from ft; Query OK, 6144 rows affected (0.13 sec) Records: 6144 Duplicates: 0 Warnings: 0 mysql 5.7 > insert into ft(c1) select c1 from ft; Query OK, 12288 rows affected (0.40 sec) Records: 12288 Duplicates: 0 Warnings: 0 mysql 5.7 > insert into ft(c1) select c1 from ft; Query OK, 24576 rows affected (2.16 sec) Records: 24576 Duplicates: 0 Warnings: 0 mysql 5.7 > insert into ft(c1) select c1 from ft; Query OK, 49152 rows affected (1.85 sec) Records: 49152 Duplicates: 0 Warnings: 0 mysql 5.7 > insert into ft(c1) select c1 from ft; Query OK, 98304 rows affected (4.37 sec) Records: 98304 Duplicates: 0 Warnings: 0 mysql 5.7 > insert into ft(c1) select c1 from ft; Query OK, 196608 rows affected (13.55 sec) Records: 196608 Duplicates: 0 Warnings: 0 mysql 5.7 > show table status like 'ft'\G *************************** 1. row *************************** Name: ft Engine: InnoDB Version: 10 Row_format: Dynamic Rows: 385999 Avg_row_length: 311 Data_length: 120209408 Max_data_length: 0 Index_length: 7880704 Data_free: 5242880 Auto_increment: 524267 Create_time: 2017-04-22 21:39:08 Update_time: 2017-04-22 21:41:55 Check_time: NULL Collation: utf8_general_ci Checksum: NULL Create_options: Comment: 1 row in set (0.00 sec) mysql 5.7 > set global innodb_ft_result_cache_limit=10000000; Query OK, 0 rows affected (0.00 sec) mysql 5.7 > explain select id from ft where match(c1) against('order') limit 5; ERROR 188 (HY000): FTS query exceeds result cache limit mysql 5.7 > explain select * from ft where match(c1) against('order') limit 5; ERROR 188 (HY000): FTS query exceeds result cache limit mysql 5.7 > set global innodb_ft_result_cache_limit=100000000; Query OK, 0 rows affected (0.00 sec) mysql 5.7 > explain select * from ft where match(c1) against('order') limit 5\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: ft partitions: NULL type: fulltext possible_keys: k key: k key_len: 0 ref: const rows: 1 filtered: 100.00 Extra: Using where; Ft_hints: sorted, limit = 5 1 row in set, 1 warning (0.12 sec) mysql 5.7 > explain select id from ft where match(c1) against('order') limit 5\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: ft partitions: NULL type: fulltext possible_keys: k key: k key_len: 0 ref: const rows: 1 filtered: 100.00 Extra: Using where; Ft_hints: sorted, limit = 5 1 row in set, 1 warning (0.08 sec) mysql 5.7 > select id from ft where match(c1) against('order') limit 5; +----+ | id | +----+ | 2 | | 5 | | 8 | | 11 | | 15 | +----+ 5 rows in set (0.17 sec) mysql 5.7 > show create table ft\G *************************** 1. row *************************** Table: ft Create Table: CREATE TABLE `ft` ( `id` int(11) NOT NULL AUTO_INCREMENT, `c1` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`), FULLTEXT KEY `k` (`c1`) ) ENGINE=InnoDB AUTO_INCREMENT=524267 DEFAULT CHARSET=utf8 1 row in set (0.00 sec)
[23 Apr 2017 0:52]
MySQL Verification Team
Thank you for the bug report.
[13 Nov 2018 7:55]
MySQL Verification Team
https://bugs.mysql.com/bug.php?id=93168 marked as duplicate of this one.