MySQL Bugs: #112597: Slow performance

Bug #112597	Slow performance
Submitted:	3 Oct 2023 12:14	Modified:	5 Oct 2023 14:13
Reporter:	Ammar Hadj Amor	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S5 (Performance)
Version:	8.0	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	performace

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. 
Do you have any explanation for the speed problem.
I can provide the script and the data used for testing.

How to repeat:
- install version 8.0.34 or 8.1
- create big table with hundred thousands of rows.
- create index for the sort by column
- select with limit and order by 
response time was more than 8s

Hi Mr. Amor,

Thank you for your bug report.

However, this is a forum for the bugs with repeatable test cases.

Hence, you have to provide one. A test case should consist of a set of SQL commands that can be run on the latest 5.7, 8.0 and 8.1, so that it would always show a significant performance reduction, we could verify this report as a performance improvement bug report.

Hence, we need table structure, their rows and the exact query.

When you provide all this info, we will then proceed in processing this report further.

Can't repeat.

Zipped sql file containing table structure and data (210 000 rows)

Attachment: mysql_sample.zip (application/x-zip-compressed, text), 31.81 MiB.

Hi,
thanks for your quick reply.
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.

Hi Mr. Amor,

Can you just send us the EXPLAINs from both versions, so that we can compare them with our EXPLAINS ???

Many, many thanks in advance.

Hi,

Two more notes.

You are using MyISAM, which is no longer maintained. Hence, try again with InnoDB.

Second, you are sorting by name, but your index is too short in comparison with the definition.

Hence, it most probably is not used. Try setting index on that column without constraints. Hence, filesort might be required, instead of the usage of index.

We are waiting on your feedback.

Bug #112634 marked as duplicate of this one

Hi,

On the latest 8.0, with InnoDB and the index on `name` in its full length , these are the times:

real	0m0.032s
user	0m0.014s
sys	0m0.007s

EXPLAIN clearly shows that the index is used.

With INDEX on name being restricted, the entire table is scanned and you get the following results:

real	0m15.173s
user	0m2.993s
sys	0m0.229s

/release/runtime_output_directory/mysql -usinisa -pSatkica_00 test -e "explain SELECT *  FROM  table_33 ORDER BY NAME  ASC LIMIT 50;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+-------------+----------+------------+------+---------------+------+---------+------+--------+----------+----------------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra          |
+----+-------------+----------+------------+------+---------------+------+---------+------+--------+----------+----------------+
|  1 | SIMPLE      | table_33 | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 194067 |   100.00 | Using filesort |
+----+-------------+----------+------------+------+---------------+------+---------+------+--------+----------+----------------+

Hence, you were comparing the speeds of the scanning of the entire table, which is not recommendable.

With 5.7, when using InnoDB, times for sorting by index are similar, but scanning is a bit faster:

real	0m11.173s
user	0m2.133s
sys	0m0.2578

Hence, we are verifying this bug as 8.0 being a bit slower then 5.7 in scanning big tables;

Verified.

Hi, 
thanks for your reply.
Below are the result of the query explain on the two version with myisam engine:

Explain SELECT *  FROM  table_33 ORDER BY NAME  ASC LIMIT 50;

- Mysql 8.0
Id   |Select_type   |table    |partitions   |type   |possible_keys   |key     |key_len    |ref    |rows    |filtered   |Extra
1     SIMPLE         table_33  (NULL)        ALL     (NULL)           (NULL)   (NULL)      (NULL)  216,214  100.0       Using filesort

- Mysql 8.1
Id   |Select_type   |table    |partitions   |type   |possible_keys   |key     |key_len    |ref    |rows    |filtered   |Extra
1     SIMPLE         table_33  (NULL)        ALL     (NULL)           (NULL)   (NULL)      (NULL)  216,214  100.0       Using filesort

I also changed the length of the index to 300 and nothing changed.

I changed the engine in both version to InnoDB and below are the results:

-MySQL 5.7:
/* Affected rows: 0  Found rows: 50  Warnings: 0  Duration for 1 query: 0.891 sec. */

-MySQL 8.0
/* Affected rows: 0  Found rows: 50  Warnings: 0  Duration for 1 query: 2.109 sec. */

Finally, the result of the explain query again innodb:
- Mysql 8.0
Id   |Select_type   |table    |partitions   |type   |possible_keys   |key     |key_len    |ref    |rows    |filtered   |Extra
1     SIMPLE         table_33  (NULL)        ALL     (NULL)           (NULL)   (NULL)      (NULL)  194,044  100.0       Using filesort

- Mysql 8.1
Id   |Select_type   |table    |partitions   |type   |possible_keys   |key     |key_len    |ref    |rows    |filtered   |Extra
1     SIMPLE         table_33  (NULL)        ALL     (NULL)           (NULL)   (NULL)      (NULL)  194,067  100.0       Using filesort

We can notice that the rows number of the explain query with innodb is different between versions 5.7 and 8.0 and it is also different from the myisam (216,214) version which is equal in the 2 versions.
I hope this could help.
Thanks in advance.

Hi,

Changing length of the index to 300 is irrelevant !!!!!

Your columns is 1024 characters long, hence you are scanning the entire table all the time.

That is not the optimal schema and query design.

This bug is already verified for the faster table scanning in 5.7 then in 8.0.

the comparaison was done between version 5.7 and 8.0.
- Mysql 5.7
Id   |Select_type   |table    |partitions   |type   |possible_keys   |key     |key_len    |ref    |rows    |filtered   |Extra
1     SIMPLE         table_33  (NULL)        ALL     (NULL)           (NULL)   (NULL)      (NULL)  216,214  100.0       Using filesort

- Mysql 8.0
Id   |Select_type   |table    |partitions   |type   |possible_keys   |key     |key_len    |ref    |rows    |filtered   |Extra
1     SIMPLE         table_33  (NULL)        ALL     (NULL)           (NULL)   (NULL)      (NULL)  216,214  100.0       Using filesort

Hi,
changning the column length from 1024 to 100 does not affect the performance for the current table im using for test.
I also changed the index to fulltext and nothing changed.