Huge InnoDB slowdown when selecting strings without indexes

[Sveta Smirnova]

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)

[Laurynas Biveinis (laurynas-biveinis)]

Upstream bug # ?

[Sveta Smirnova (svetasmirnova)]

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

[Sveta Smirnova (svetasmirnova)]

Using VARCHAR column instead of TEXT changes nothing.

[Shahriyar Rzayev (rzayev-sehriyar)]

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