| Bug #112902 | Optimizer in MySQL 8 is significantly slower on dependent subqueries than 5.7 | ||
|---|---|---|---|
| Submitted: | 31 Oct 2023 14:39 | Modified: | 26 Feb 2024 9:39 |
| Reporter: | Oli Sennhauser | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S5 (Performance) |
| Version: | 8.0.31, 8.0.35 | OS: | Linux (n.a.) |
| Assigned to: | CPU Architecture: | x86 (x86_64) | |
| Tags: | dependent subquery, Optimizer, performance | ||
[31 Oct 2023 15:11]
MySQL Verification Team
HI Mr. Sennhauser, Thank you very much for your bug report. Can you show us the times of execution for 5.7 and 8.0. Also, we need a repeatable test case in the order in which it is to be executed. That is to say, first CREATE TABLE for each of tables, then INSERTs and then the queries and their duration on your setup. We also do not understand why is UPDATE necesary. Also, let us know whether the results are reproducible with smaller number of rows. Thanks in advance.
[31 Oct 2023 15:33]
Oli Sennhauser
* Execution times (in seconds): - 5.7: 5.77, 6.54, 6.45, 5.97 - 8.0: 45.40, 46.14, 46.36, 46.17 * Repeatable test case: See: "How to repeat". If not clear, please let me know exactly what is not clear. * The UPDATE fills the BLOB columns. With empty BLOB column we do not see any difference. * Reproducing with smaller number of rows is not relevant to us because this is the dataset which is relevant in production. It takes less than 1 hour to create the amount of data needed...
[31 Oct 2023 15:35]
MySQL Verification Team
Hi, Just for your information, testing this report will take some time .......
[1 Nov 2023 11:09]
MySQL Verification Team
Hi Mr. Sennhauser, We have done some analysis of your test case. We think that it requires changes. First of all, standard settings have changed between 5.7 and 8.0. You have set UTF as a character set. In 5.7 it could be UTF8 or utf8mb3, while in 8.0 this is automatically converted to utf8mb4. Hence, you will get 33 % slower performance with 8.0 just with that fact. In order to level up the playing field, can you try rerunning your test cases for both 5.7 and 8.0 with latin1 character set. That would set the same playing field, otherwise the speed difference of 25 - 30 % , between 5.7 and 8.0, would be fully expected behaviour and not a bug. Please do note that ROLE_VIEWERS_RELEASE_ID is longer in 8.0 then in 5.7. The same holds true for MBER_VIEWERES_RELEASE_ID index. This is due to the difference in 3 versus 4 bytes of the character length. Hence, we are comparing apples and oranges here ....... Next, if we take a look at the explains for the same query, we can see that (in both cases) the entire table is scanned. However, you have 70.000 more rows in the table generated for 8.0 then the one in 5.7. Of oure it will take more time to scan 70.000 more rows. In order to have the same level of the playing field, you should have tables of the same size !!!!! Keeping in mind the larger width of the columns and indices and the larger number of rows, a difference of that magnitude is expected behaviour. Next, you should try using new features from 8.0. After creating tables you should shutdown MySQL and change your configuration so that innodb_buffer_pool_load_at_startup is set to ON. Then run your query. If that does not help, then please, try using new optimiser hints from 8.0. Please try using one by one , from this page: https://dev.mysql.com/doc/refman/8.0/en/optimizer-hints.html Let us know if anything of this helps. We are waiting on your feedback
[1 Nov 2023 14:03]
Roy Lyseng
Hi Oli,
there is a "double equality" in your query concerning releaseId
releases.releaseId IN (SELECT releaseId
FROM member_viewers members
WHERE members.releaseId = releases.releaseId
Have you tried rewriting this with EXISTS, like:
EXISTS (SELECT releaseId
FROM member_viewers members
WHERE members.releaseId = releases.releaseId
The rewrite may be a possible workaround, but of course it does not change the state of this problem as a potensial bug.
[1 Nov 2023 14:29]
Oli Sennhauser
Hi Roy Thank you for your suggestion. Unfortunately rewriting the query is not an option because it is a third party software... Otherwise we would have done it already! :-) Btw. hinting the query is also not an option because of the same reason. Enabling/disabling some optimizer switches could be an option. But I did not find any hint controlling this behaviour. Or the documentation did not lead me to the right hint... I compared the optimizer code between 5.7 and 8.0 and there is one part in the 5.7 code missing which is possibly responsible for the different execution plans. Best Regards, Oli
[1 Nov 2023 14:33]
MySQL Verification Team
Hi Mr. Sennhauser, We did send you a long comment with a number of questions and suggestions on which we did not receive any answer.
[1 Nov 2023 14:35]
MySQL Verification Team
Hi, Please especially pay attention that the width of the columns in 8.0 is much larger than in 5.7 and that the number of rows in your 8.0 schema is significantly larger. We have asked other questions as well.
[2 Nov 2023 5:58]
MySQL Verification Team
Hello Oli, Thank you for the report and feedback. I quickly tried to reproduce the issue with the provided information but not seeing the reported performance drop as you shared in the report(5.7: 5.77, 6.54, 6.45, 5.97 & 8.0: 45.40, 46.14, 46.36, 46.17). I was running the MySQL Server instances on a large test setup with the default settings, dummy test case and that could be the reason for not seeing the exact perf drop which you are witnessing at your end. May I request you to please share exact configuration files used for both 5.7/8.0 for the tests? I'm currently trying with default settings(but comparison will be not accurate since binary logging is off by default in 5.7 and is on by default in 8.0 and diff in charset as well). Also, the exact 5.7 version details. >> We have a dump (290 M compressed, 16G uncompressed) which can be provided. This would be really useful if you can provide compressed dump to reproduce the issue at our end. More details are in the "Files" section of this bug page which explains how to attach large files to the bug. Thank you. Sincerely, Umesh
[2 Nov 2023 6:01]
MySQL Verification Team
-- Earlier test results
## 5.7.44 ( using current GA as which exact 5.7 build used is not clear)
cat docs/INFO_SRC
commit: c3d73b7cfe6768e9c6dfbb91e2ad59d78f0dbf60
date: 2023-09-20 21:27:05 +0200
build-date: 2023-10-11 11:43:10 +0000
short: c3d73b7cfe6
branch: mysql-5.7.44-release
MySQL source 5.7.44
mysql> pager grep rows
PAGER set to 'grep rows'
mysql>
mysql> SELECT releaseJson FROM releases releases WHERE ( releases.releaseId IN ( SELECT releaseId FROM member_viewers members WHERE members.releaseId = releases.releaseId AND members.username = 'viewer' )OR releases.releaseId IN ( SELECT releaseId FROM role_viewers roles WHERE roles.releaseId = releases.releaseId AND roles.roleName IN ('XLRViewer') )) AND (releases.status = 'aborted' OR releases.status = 'completed') ORDER BY endDate DESC, releaseId ASC LIMIT 15 OFFSET 0;
15 rows in set (8.18 sec)
mysql> SELECT releaseJson FROM releases releases WHERE ( releases.releaseId IN ( SELECT releaseId FROM member_viewers members WHERE members.releaseId = releases.releaseId AND members.username = 'viewer' )OR releases.releaseId IN ( SELECT releaseId FROM role_viewers roles WHERE roles.releaseId = releases.releaseId AND roles.roleName IN ('XLRViewer') )) AND (releases.status = 'aborted' OR releases.status = 'completed') ORDER BY endDate DESC, releaseId ASC LIMIT 15 OFFSET 0;
15 rows in set (8.40 sec)
.
mysql> SELECT releaseJson FROM releases releases WHERE ( releases.releaseId IN ( SELECT releaseId FROM member_viewers members WHERE members.releaseId = releases.releaseId AND members.username = 'viewer' )OR releases.releaseId IN ( SELECT releaseId FROM role_viewers roles WHERE roles.releaseId = releases.releaseId AND roles.roleName IN ('XLRViewer') )) AND (releases.status = 'aborted' OR releases.status = 'completed') ORDER BY endDate DESC, releaseId ASC LIMIT 15 OFFSET 0;
15 rows in set (8.41 sec)
## 8.0.31 (reported instance)
-- build used
system cat docs/INFO_SRC
commit: da4abf34c335274d18a2a8b2d6c9a677a498b15b
date: 2022-09-13 18:06:48 +0200
build-date: 2022-09-13 16:16:26 +0000
short: da4abf34c33
branch: mysql-8.0.31-release
MySQL source 8.0.31
-
mysql> pager grep row
PAGER set to 'grep row'
mysql> SELECT releaseJson FROM releases releases WHERE ( releases.releaseId IN ( SELECT releaseId FROM member_viewers members WHERE members.releaseId = releases.releaseId AND members.username = 'viewer' )OR releases.releaseId IN ( SELECT releaseId FROM role_viewers roles WHERE roles.releaseId = releases.releaseId AND roles.roleName IN ('XLRViewer') )) AND (releases.status = 'aborted' OR releases.status = 'completed') ORDER BY endDate DESC, releaseId ASC LIMIT 15 OFFSET 0;
15 rows in set (10.52 sec)
mysql> SELECT releaseJson FROM releases releases WHERE ( releases.releaseId IN ( SELECT releaseId FROM member_viewers members WHERE members.releaseId = releases.releaseId AND members.username = 'viewer' )OR releases.releaseId IN ( SELECT releaseId FROM role_viewers roles WHERE roles.releaseId = releases.releaseId AND roles.roleName IN ('XLRViewer') )) AND (releases.status = 'aborted' OR releases.status = 'completed') ORDER BY endDate DESC, releaseId ASC LIMIT 15 OFFSET 0;
15 rows in set (10.46 sec)
.
mysql> SELECT releaseJson FROM releases releases WHERE ( releases.releaseId IN ( SELECT releaseId FROM member_viewers members WHERE members.releaseId = releases.releaseId AND members.username = 'viewer' )OR releases.releaseId IN ( SELECT releaseId FROM role_viewers roles WHERE roles.releaseId = releases.releaseId AND roles.roleName IN ('XLRViewer') )) AND (releases.status = 'aborted' OR releases.status = 'completed') ORDER BY endDate DESC, releaseId ASC LIMIT 15 OFFSET 0;
15 rows in set (10.44 sec)
mysql> SELECT releaseJson FROM releases releases WHERE ( releases.releaseId IN ( SELECT releaseId FROM member_viewers members WHERE members.releaseId = releases.releaseId AND members.username = 'viewer' )OR releases.releaseId IN ( SELECT releaseId FROM role_viewers roles WHERE roles.releaseId = releases.releaseId AND roles.roleName IN ('XLRViewer') )) AND (releases.status = 'aborted' OR releases.status = 'completed') ORDER BY endDate DESC, releaseId ASC LIMIT 15 OFFSET 0;
15 rows in set (10.37 sec)
[2 Nov 2023 7:55]
Oli Sennhauser
To the question of [1 Nov 11:09] MySQL Verification Team * We know the differences between latin1, utf8mb3 and uf8mb4 and that queries become slower. But here we are talking about 20-40% slower not about factor of 10-60. * As far as I remember it was Oracle/MySQL which recommended (default!) to switch from utf8mb3 to utf8mb4...? * latin1 is not an option. * We also tried the mysql-57 dataset in MySQL 8.0 (exactly same data) and we got the same results. * innodb_buffer_pool_at_startup is already enabled by default. And because we are running the query multiple times and skipping the first run it does not have an effect at all. * Hinting is not an option because this is a third party software we cannot change.
[2 Nov 2023 7:58]
Oli Sennhauser
Hi Umesh Did you fill the BLOB columns as well? We only have seen the problem when they contained some data... 8.0 causes significant more I/O than 5.7 so if you have huge amount of RAM and fast SSD the effect is possibly smaller... Config will be attached soon...
[2 Nov 2023 8:04]
MySQL Verification Team
Hello Oli, Thank you for attaching the requested files. I'll try to reproduce with the provided details and would get back to you if anything further needed. Thank you. Sincerely, Umesh
[3 Nov 2023 9:49]
MySQL Verification Team
Hello Oli,
I'm able to reproduce the issue at my end using provided data, conf file but seeing only 3x/4x slowness compared to what you observed i.e. 10-60 factor(but like you said, huge amount of RAM and fast SSD the effect is possibly smaller as I was using moderately high test box with 120GB RAM, vCPU 16 etc.). I'll try to see later if I can get a smaller and even huge box than the one used to see how it behaves. For now verifying this report.
Thanks to Roy for all his advise throughout this verification at my end.
Also, I have a request for you - Is it possible for you run through these against 5.7/8.0 and share details?
flush status;
SELECT releaseJson FROM releases releases WHERE ( releases.releaseId IN ( SELECT releaseId FROM member_viewers members WHERE members.releaseId = releases.releaseId AND members.username = 'viewer' )OR releases.releaseId IN ( SELECT releaseId FROM role_viewers roles WHERE roles.releaseId = releases.releaseId AND roles.roleName IN ('XLRViewer') )) AND (releases.status = 'aborted' OR releases.status = 'completed') ORDER BY endDate DESC, releaseId ASC LIMIT 15 OFFSET 0;
show status like '%handler%';
Also please do run EXPLAIN ANALYZE on the 8.0 instance and share with us.
Sincerely,
Umesh
[3 Nov 2023 10:16]
Oli Sennhauser
Hi Umesh
Thank you and Roy for reproducing and it is a pleasure for me to help you hunting the bug...
5.7
+----------------------------+---------+
| Variable_name | Value |
+----------------------------+---------+
| Handler_commit | 1 |
| Handler_delete | 0 |
| Handler_discover | 0 |
| Handler_external_lock | 6 |
| Handler_mrr_init | 0 |
| Handler_prepare | 0 |
| Handler_read_first | 1 |
| Handler_read_key | 524304 |
| Handler_read_last | 0 |
| Handler_read_next | 2097152 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 15 |
| Handler_read_rnd_next | 524289 |
| Handler_rollback | 0 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_update | 0 |
| Handler_write | 0 |
+----------------------------+---------+
8.0
+----------------------------+---------+
| Variable_name | Value |
+----------------------------+---------+
| Handler_commit | 1 |
| Handler_delete | 0 |
| Handler_discover | 0 |
| Handler_external_lock | 6 |
| Handler_mrr_init | 0 |
| Handler_prepare | 0 |
| Handler_read_first | 1 |
| Handler_read_key | 524291 |
| Handler_read_last | 0 |
| Handler_read_next | 2097100 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 15 |
| Handler_read_rnd_next | 524276 |
| Handler_rollback | 0 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_update | 0 |
| Handler_write | 0 |
+----------------------------+---------+
No significant difference. :-(
-> Limit: 15 row(s) (cost=67194.22 rows=15) (actual time=47456.420..47457.206 rows=15 loops=1)
-> Sort row IDs: releases.endDate DESC, releases.releaseId, limit input to 15 row(s) per chunk (cost=67194.22 rows=612994) (actual time=47456.416..47457.201 rows=15 loops=1)
-> Filter: ((<in_optimizer>(releases.releaseId,<exists>(select #2)) or <in_optimizer>(releases.releaseId,<exists>(select #3))) and ((releases.`status` = 'aborted') or (releases.`status` = 'completed'))) (cost=67194.22 rows=612994) (actual time=3.121..47049.357 rows=524275 loops=1)
-> Table scan on releases (cost=67194.22 rows=612994) (actual time=0.140..35635.280 rows=524275 loops=1)
-> Select #2 (subquery in condition; dependent)
-> Limit: 1 row(s) (cost=4.15 rows=1) (actual time=0.021..0.021 rows=1 loops=524275)
-> Filter: ((members.username = 'viewer') and (<cache>(releases.releaseId) = members.releaseId)) (cost=4.15 rows=1) (actual time=0.021..0.021 rows=1 loops=524275)
-> Index lookup on members using MEMBER_VIEWERES_RELEASE_ID (releaseId=releases.releaseId) (cost=4.15 rows=10) (actual time=0.008..0.020 rows=5 loops=524275)
-> Select #3 (subquery in condition; dependent)
-> Limit: 1 row(s) (cost=8.63 rows=1) (never executed)
-> Filter: ((roles.roleName = 'XLRViewer') and (<cache>(releases.releaseId) = roles.releaseId)) (cost=8.63 rows=1) (never executed)
-> Index lookup on roles using ROLE_VIEWERS_RELEASE_ID (releaseId=releases.releaseId) (cost=8.63 rows=14) (never executed)
+-------+------+------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+------------------------------------------------------------------------------------+
| Note | 1276 | Field or reference 'grz.releases.releaseId' of SELECT #2 was resolved in SELECT #1 |
| Note | 1276 | Field or reference 'grz.releases.releaseId' of SELECT #3 was resolved in SELECT #1 |
+-------+------+------------------------------------------------------------------------------------+
[3 Nov 2023 13:26]
Oli Sennhauser
Hi Umesh
I also got the results from the real production system know:
5.7
Query run time: Empty set (1 min 35.98 sec)
+----------------------------+----------+
| Variable_name | Value |
+----------------------------+----------+
| Handler_commit | 1 |
| Handler_delete | 0 |
| Handler_discover | 0 |
| Handler_external_lock | 6 |
| Handler_mrr_init | 0 |
| Handler_prepare | 0 |
| Handler_read_first | 1 |
| Handler_read_key | 878869 |
| Handler_read_last | 0 |
| Handler_read_next | 11795710 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 439435 |
| Handler_rollback | 0 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_update | 0 |
| Handler_write | 0 |
+----------------------------+----------+
8.0
Query run time: Empty set (51 min 2.19 sec) -> Here comes the factor 60 I was writing of (OK it is "only" factor 32 and not 60 to be precise :-D)
+----------------------------+----------+
| Variable_name | Value |
+----------------------------+----------+
| Handler_commit | 1 |
| Handler_delete | 0 |
| Handler_discover | 0 |
| Handler_external_lock | 6 |
| Handler_mrr_init | 0 |
| Handler_prepare | 0 |
| Handler_read_first | 1 |
| Handler_read_key | 878869 |
| Handler_read_last | 0 |
| Handler_read_next | 11795710 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 439435 |
| Handler_rollback | 0 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_update | 0 |
| Handler_write | 0 |
+----------------------------+----------+
-> Limit: 15 row(s) (cost=9.47e+6 rows=15) (actual time=3.02e+6..3.02e+6 rows=0 loops=1)
-> Sort row IDs: releases.endDate DESC, releases.releaseId, limit input to 15 row(s) per chunk (cost=9.47e+6 rows=487720) (actual time=3.02e+6..3.02e+6 rows=0 loops=1)
-> Filter: ((<in_optimizer>(releases.releaseId,<exists>(select #2)) or <in_optimizer>(releases.releaseId,<exists>(select #3))) and ((releases.`status` = 'aborted') or (releases.`status` = 'completed'))) (cost=9.47e+6 rows=487720) (actual time=3.02e+6..3.02e+6 rows=0 loops=1)
-> Table scan on releases (cost=9.47e+6 rows=487720) (actual time=3.22..2.95e+6 rows=439434 loops=1)
-> Select #2 (subquery in condition; dependent)
-> Limit: 1 row(s) (cost=3.39 rows=1) (actual time=0.0596..0.0596 rows=0 loops=439434)
-> Filter: ((members.username = 'viewer') and (<cache>(releases.releaseId) = members.releaseId)) (cost=3.39 rows=1.31) (actual time=0.0592..0.0592 rows=0 loops=439434)
-> Index lookup on members using MEMBER_VIEWERES_RELEASE_ID (releaseId=releases.releaseId) (cost=3.39 rows=13.1) (actual time=0.014..0.0577 rows=10.8 loops=439434)
-> Select #3 (subquery in condition; dependent)
-> Limit: 1 row(s) (cost=3.89 rows=1) (actual time=0.102..0.102 rows=0 loops=439434)
-> Filter: ((roles.roleName = 'XLRViewer') and (<cache>(releases.releaseId) = roles.releaseId)) (cost=3.89 rows=1.5) (actual time=0.102..0.102 rows=0 loops=439434)
-> Index lookup on roles using ROLE_VIEWERS_RELEASE_ID (releaseId=releases.releaseId) (cost=3.89 rows=15) (actual time=0.013..0.1 rows=16 loops=439434)
1 row in set, 2 warnings (50 min 18.75 sec)
+-------+------+-----------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+-----------------------------------------------------------------------------------------------+
| Note | 1276 | Field or reference 'xlarchive_prod.releases.releaseId' of SELECT #2 was resolved in SELECT #1 |
| Note | 1276 | Field or reference 'xlarchive_prod.releases.releaseId' of SELECT #3 was resolved in SELECT #1 |
+-------+------+-----------------------------------------------------------------------------------------------+
And now I also see the difference between my simulation to the real production query... Select #2 and #3 are both evil. In my case only Select #2 was evil... Possibly this is the explanation why 5.7 was faster?
[3 Nov 2023 13:36]
MySQL Verification Team
Thank you, Oli for all the details. Sincerely, Umesh
[26 Feb 2024 9:39]
Oli Sennhauser
Hi Umesh Is there any prediction when we will hear news about this ticket? Best Regards, Oli
[26 Feb 2024 11:07]
MySQL Verification Team
Hi Mr. Sennhauser, Regarding the schedule, each Development team has it's own schedule, which is changing weekly. Hence, nobody can give you, right now, any date about when will Development team in charge start working on the performance bugs, including this one. However, we shall send a query and when this report is in the works, we shall let you know.
Description: After upgrading from MySQL 5.7 to MySQL 8.0 one dependent subquery is significantly slower (factor 10 - 60) than before. Query Execution Plan looks nearly! the same. Buffer pool size is the same, machine is the same. Details below. But work performed looks different. ANALYZE TABLE did not help. We did not find any way to hint the query to work as before. And it looks like the part which is different was optimized away during the upgrade. It seems like the BLOB columns cause the main difference. When we filled up the BLOB columns it started to become slower. We can reproduce it and handover a test case if wanted/needed. How to repeat: SELECT releaseJson FROM releases releases WHERE ( releases.releaseId IN ( SELECT releaseId FROM member_viewers members WHERE members.releaseId = releases.releaseId AND members.username = 'viewer' )OR releases.releaseId IN ( SELECT releaseId FROM role_viewers roles WHERE roles.releaseId = releases.releaseId AND roles.roleName IN ('XLRViewer') )) AND (releases.status = 'aborted' OR releases.status = 'completed') ORDER BY endDate DESC, releaseId ASC LIMIT 15 OFFSET 0; CREATE TABLE `releases` ( `releaseId` varchar(255) NOT NULL, `releaseJson` mediumblob, `activityLogs` mediumblob, `releaseTitle` varchar(1024) NOT NULL, `startDate` timestamp NULL DEFAULT NULL, `endDate` timestamp NULL DEFAULT NULL, `duration` bigint(20) DEFAULT NULL, `monthYear` varchar(10) DEFAULT NULL, `releaseOwner` varchar(1024) DEFAULT NULL, `status` varchar(256) NOT NULL, `manualTasksCount` int(11) NOT NULL, `automatedTasksCount` int(11) NOT NULL, `manualTasksDuration` bigint(20) NOT NULL, `automatedTasksDuration` bigint(20) NOT NULL, `isFlagged` smallint(6) NOT NULL, `originTemplateId` varchar(255) DEFAULT NULL, PRIMARY KEY (`releaseId`), KEY `IDX_AR_ORGINAL_TEMPLATE_ID` (`originTemplateId`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ; CREATE TABLE `member_viewers` ( `username` varchar(1024) NOT NULL, `releaseId` varchar(255) NOT NULL, KEY `MEMBER_VIEWERES_RELEASE_ID` (`releaseId`), CONSTRAINT `MEMBER_VIEWERES_RELEASE_ID` FOREIGN KEY (`releaseId`) REFERENCES `releases` (`releaseId`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ; CREATE TABLE `role_viewers` ( `roleName` varchar(1024) DEFAULT NULL, `releaseId` varchar(255) NOT NULL, KEY `ROLE_VIEWERS_RELEASE_ID` (`releaseId`), CONSTRAINT `ROLE_VIEWERS_RELEASE_ID` FOREIGN KEY (`releaseId`) REFERENCES `releases` (`releaseId`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ; releases => 439434 rows member_viewers => 4753338 rows role_viewers => 7042372 rows Data generation: INSERT INTO releases VALUES (md5(sysdate(6)), '', '', 'Bla title', now(), now(), 124446, 'Midzember', 'Oli Sennhauser', 'aborted', 0, 9, 43, 44, 1, md5(now(6))); INSERT IGNORE INTO releases SELECT md5(sysdate(6)), '', '', 'Bla title', now(), now(), 124446, 'Midzember', 'Oli Sennhauser', 'completed', 0, 9, 43, 44, 1, md5(now(6)) FROM releases; INSERT IGNORE INTO releases SELECT md5(sysdate(6)), '', '', 'Bla title', now(), now(), 124446, 'Midzember', 'Oli Sennhauser', 'aborted', 0, 9, 43, 44, 1, md5(now(6)) FROM releases; INSERT INTO member_viewers SELECT 'Oli Sennhauser1', releaseId from releases; INSERT INTO member_viewers SELECT 'Oli Sennhauser2', releaseId from releases; INSERT INTO member_viewers SELECT 'Oli Sennhauser3', releaseId from releases; INSERT INTO member_viewers SELECT 'Oli Sennhauser4', releaseId from releases; INSERT INTO member_viewers SELECT 'viewer', releaseId from releases; INSERT INTO member_viewers SELECT 'Oli Sennhauser6', releaseId from releases; INSERT INTO member_viewers SELECT 'Oli Sennhauser7', releaseId from releases; INSERT INTO member_viewers SELECT 'Oli Sennhauser8', releaseId from releases; INSERT INTO member_viewers SELECT 'Oli Sennhauser9', releaseId from releases; INSERT INTO member_viewers SELECT 'Oli Sennhauser10', releaseId from releases; INSERT INTO role_viewers SELECT 'Dummy role01', releaseId from releases; INSERT INTO role_viewers SELECT 'Dummy role02', releaseId from releases; INSERT INTO role_viewers SELECT 'Dummy role03', releaseId from releases; INSERT INTO role_viewers SELECT 'Dummy role04', releaseId from releases; INSERT INTO role_viewers SELECT 'Dummy role05', releaseId from releases; INSERT INTO role_viewers SELECT 'Dummy role06', releaseId from releases; INSERT INTO role_viewers SELECT 'Dummy role07', releaseId from releases; INSERT INTO role_viewers SELECT 'XLRViewer', releaseId from releases; INSERT INTO role_viewers SELECT 'Dummy role09', releaseId from releases; INSERT INTO role_viewers SELECT 'Dummy role10', releaseId from releases; INSERT INTO role_viewers SELECT 'Dummy role11', releaseId from releases; INSERT INTO role_viewers SELECT 'Dummy role12', releaseId from releases; INSERT INTO role_viewers SELECT 'Dummy role13', releaseId from releases; INSERT INTO role_viewers SELECT 'Dummy role14', releaseId from releases; INSERT INTO role_viewers SELECT 'Dummy role15', releaseId from releases; ANALYZE TABLE releases; ANALYZE TABLE member_viewers; ANALYZE TABLE role_viewers; UPDATE releases SET releaseJson = repeat('0123456789012345678901234567890123456789', 10000), activityLogs = repeat('0123456789012345678901234567890123456789', 10000) WHERE releaseJson IS NOT NULL LIMIT 10000; QEP 5.7: +----+--------------------+----------+------------+------+----------------------------+----------------------------+---------+------------------------+--------+----------+-----------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+--------------------+----------+------------+------+----------------------------+----------------------------+---------+------------------------+--------+----------+-----------------------------+ | 1 | PRIMARY | releases | NULL | ALL | NULL | NULL | NULL | NULL | 542006 | 19.00 | Using where; Using filesort | | 3 | DEPENDENT SUBQUERY | roles | NULL | ref | ROLE_VIEWERS_RELEASE_ID | ROLE_VIEWERS_RELEASE_ID | 767 | grz.releases.releaseId | 13 | 10.00 | Using where | | 2 | DEPENDENT SUBQUERY | members | NULL | ref | MEMBER_VIEWERES_RELEASE_ID | MEMBER_VIEWERES_RELEASE_ID | 767 | grz.releases.releaseId | 9 | 10.00 | Using where | +----+--------------------+----------+------------+------+----------------------------+----------------------------+---------+------------------------+--------+----------+-----------------------------+ QEP 8.0: +----+--------------------+----------+------------+------+----------------------------+----------------------------+---------+------------------------+--------+----------+-----------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+--------------------+----------+------------+------+----------------------------+----------------------------+---------+------------------------+--------+----------+-----------------------------+ | 1 | PRIMARY | releases | NULL | ALL | NULL | NULL | NULL | NULL | 612994 | 19.00 | Using where; Using filesort | | 3 | DEPENDENT SUBQUERY | roles | NULL | ref | ROLE_VIEWERS_RELEASE_ID | ROLE_VIEWERS_RELEASE_ID | 1022 | grz.releases.releaseId | 14 | 10.00 | Using where | | 2 | DEPENDENT SUBQUERY | members | NULL | ref | MEMBER_VIEWERES_RELEASE_ID | MEMBER_VIEWERES_RELEASE_ID | 1022 | grz.releases.releaseId | 9 | 10.00 | Using where | +----+--------------------+----------+------------+------+----------------------------+----------------------------+---------+------------------------+--------+----------+-----------------------------+ The only difference we can see is when we execute show warnings after EXPLAIN: QEP 5.7: /* select#1 */ select `grz`.`releases`.`releaseJson` AS `releaseJson` from `grz`.`releases` where ((<in_optimizer>(`grz`.`releases`.`releaseId`,<exists>(/* select#2 */ select 1 from `grz`.`member_viewers` `members` where ((`grz`.`members`.`releaseId` = `grz`.`releases`.`releaseId`) and (`grz`.`members`.`username` = 'viewer') and (<cache>(`grz`.`releases`.`releaseId`) = `grz`.`members`.`releaseId`)))) or <in_optimizer>(`grz`.`releases`.`releaseId`,<exists>(/* select#3 */ select 1 from `grz`.`role_viewers` `roles` where ((`grz`.`roles`.`releaseId` = `grz`.`releases`.`releaseId`) and (`grz`.`roles`.`roleName` = 'XLRViewer') and (<cache>(`grz`.`releases`.`releaseId`) = `grz`.`roles`.`releaseId`))))) and ((`grz`.`releases`.`status` = 'aborted') or (`grz`.`releases`.`status` = 'completed'))) order by `grz`.`releases`.`endDate` desc,`grz`.`releases`.`releaseId` limit 0,15 QEP 8.0: /* select#1 */ select `grz`.`releases`.`releaseJson` AS `releaseJson` from `grz`.`releases` where ((<in_optimizer>(`grz`.`releases`.`releaseId`,<exists>(/* select#2 */ select `grz`.`members`.`releaseId` from `grz`.`member_viewers` `members` where ((`grz`.`members`.`releaseId` = `grz`.`releases`.`releaseId`) and (`grz`.`members`.`username` = 'viewer') and (<cache>(`grz`.`releases`.`releaseId`) = `grz`.`members`.`releaseId`)))) or <in_optimizer>(`grz`.`releases`.`releaseId`,<exists>(/* select#3 */ select `grz`.`roles`.`releaseId` from `grz`.`role_viewers` `roles` where ((`grz`.`roles`.`releaseId` = `grz`.`releases`.`releaseId`) and (`grz`.`roles`.`roleName` = 'XLRViewer') and (<cache>(`grz`.`releases`.`releaseId`) = `grz`.`roles`.`releaseId`))))) and ((`grz`.`releases`.`status` = 'aborted') or (`grz`.`releases`.`status` = 'completed'))) order by `grz`.`releases`.`endDate` desc,`grz`.`releases`.`releaseId` limit 0,15 Diff is (2 times): 5.7: select#2 */ select 1 from 8.0: select#2 */ select `grz`.`members`.`releaseId` from This is "only" a difference off 255 bytes per row (1022 - 767) and should not really matter that much. So there must be something else we do not see or understand (related to the blobs). We have a dump (290 M compressed, 16G uncompressed) which can be provided. Suggested fix: No idea.