Description:
Hi,
I am using MySQL 5.7 with big tables and the performance seems to be quite good when selecting all records using and orber by clause.
I recently installed 8.0.34 and 8.1 and both showed performance issue using the same query and the same index.
It seems that the "select * from table order by field1" query using an order by clause without any where clause seems to not use the index or could be any other issue only in version 8.
The explain query command showed that it is using filesort and not keys.
How to repeat:
Below are the step to reproduce the bug
1- create the table :
CREATE TABLE IF NOT EXISTS `table_33` (
`_id` int(11) NOT NULL,
`name` varchar(1024) DEFAULT NULL,
`read_group` bigint(20) DEFAULT '1',
`write_group` bigint(20) DEFAULT '1',
`delete_group` bigint(20) DEFAULT '1',
`changeheader_group` bigint(20) DEFAULT '1',
`creation_date` bigint(20) DEFAULT NULL,
`owner_` tinytext,
`usermod` tinytext,
`last_change_date` bigint(20) DEFAULT NULL,
`details` longtext,
`locked` int(11) NOT NULL DEFAULT '0',
`isonline` int(11) NOT NULL DEFAULT '0',
`v214` int(11) DEFAULT NULL,
`d215` double DEFAULT NULL,
`v216` int(11) DEFAULT NULL,
`e217` longtext,
`e218` longtext,
`v219` int(11) DEFAULT NULL,
`e220` longtext,
`u221` longtext,
`l222` longtext,
`n223` longblob,
`e224` longtext,
`v225` int(11) DEFAULT NULL,
`e226` longtext,
`d227` double DEFAULT NULL,
`v228` int(11) DEFAULT NULL,
`e229` longtext,
`e230` longtext,
`d231` double DEFAULT NULL,
`e232` longtext,
`e233` longtext,
`d234` double DEFAULT NULL,
`v235` int(11) DEFAULT NULL,
`e236` longtext,
`e237` longtext,
`i238` bigint(20) DEFAULT NULL,
`i239` bigint(20) DEFAULT NULL,
`d240` double DEFAULT NULL,
`d241` double DEFAULT NULL,
`e242` longtext,
`e243` longtext,
`d245` double DEFAULT NULL,
`d244` double DEFAULT NULL,
`e246` longtext,
`k247` bigint(20) DEFAULT NULL,
`v248` int(11) DEFAULT NULL,
`e249` longtext,
`s250` longtext,
`c251` longtext,
`u252` longtext,
PRIMARY KEY (`_id`),
KEY `_id_read_group_idx` (`_id`,`read_group`) USING BTREE,
KEY `name_idx` (`name`(16)) USING BTREE,
KEY `last_change_date_idx` (`last_change_date`) USING BTREE,
KEY `owner__idx` (`owner_`(8)) USING BTREE
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
2- Fill the table with data up to hundred of thousands rows or use the attached file to create and insert data
3- Run the following query against MySQL 5.7 and MySQL 8.0
SELECT * FROM table_33 ORDER BY NAME ASC LIMIT 50;
4- below are the response time for both version :
- MySQL 5.7 :
/* Affected rows: 0 Found rows: 50 Warnings: 0 Duration for 1 query: 0.328 sec. */
-MySQL 8.0 :
/* Affected rows: 0 Found rows: 50 Warnings: 0 Duration for 1 query: 3.032 sec. */
We can notice that version 8.0 is very slow compared to the version 5.7.
It is getting slower if the table contains more rows and columns.
I hope you can reproduce the bug.
Thanks for your help.
