Huge InnoDB slowdown when selecting strings without indexes
Affects | Status | Importance | Assigned to | Milestone | ||
---|---|---|---|---|---|---|
MySQL Server |
Unknown
|
Unknown
|
||||
Percona Server moved to https://jira.percona.com/projects/PS | Status tracked in 5.7 | |||||
5.5 |
Triaged
|
Medium
|
Unassigned | |||
5.6 |
Triaged
|
Medium
|
Unassigned | |||
5.7 |
Triaged
|
Medium
|
Unassigned |
Bug Description
InnoDB performs very poorly if selects string from a table without using indexes. MyISAM, however, performs 30 times faster.
How to repeat:
1. Download attached csv file
2. Start MTR with: ./mtr --start innodb --mysqld=
3. Put csv file into var/mysqld.
4. Run test:
mysql> CREATE TABLE `testtable` ( `col3` text ) ENGINE=INNODB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0,45 sec)
mysql> load data infile 'data.csv' into table testtable fields enclosed by '"' ignore 1 lines;
Query OK, 5974526 rows affected (23 min 36,62 sec)
Records: 5974526 Deleted: 0 Skipped: 0 Warnings: 0
mysql> Select * from testtable where col3 LIKE '%qwertyd%'; Select * from testtable LIMIT 59745270,10;
Empty set (5 min 6,33 sec)
Empty set (4 min 43,52 sec)
mysql> alter table testtable engine=myisam;
Query OK, 5974526 rows affected (6 min 27,87 sec)
Records: 5974526 Duplicates: 0 Warnings: 0
mysql> Select * from testtable where col3 LIKE '%qwertyd%'; Select * from testtable LIMIT 59745270,10;
Empty set (14,24 sec)
Empty set (11,08 sec)
Upstream bug # ?