Huge InnoDB slowdown when selecting strings without indexes

Bug #1714094 reported by Sveta Smirnova on 2017-08-30
10
This bug affects 2 people
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=--innodb_buffer_pool_size=2G --mysqld=--innodb_log_file_size=512M &
3. Put csv file into var/mysqld.1/data/test/
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 # ?

tags: added: performance
Sveta Smirnova (svetasmirnova) wrote :

CSV file is too large for attachment. Download it from https://drive.google.com/open?id=0B67lYkmv0ZcsU2R5eVJ5OGZ1eG8

Sveta Smirnova (svetasmirnova) wrote :

Using VARCHAR column instead of TEXT changes nothing.

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-1821

To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.