After upgrading to MySQL 5.7 (from 5.6) with Amazon RDS, some queries that use A TONS of ids (42k+) inside the IN CLAUSE started to get much slower.
Query
SELECT (`sales_contact_emails`.`contact_id`) AS `_prefetch_related_val_contact_id`,
`sales_email`.`id`,
`sales_email`.`type`,
`sales_email`.`email`
FROM `sales_email`
INNER JOIN `sales_contact_emails` ON (`sales_email`.`id` = `sales_contact_emails`.`email_id`)
WHERE `sales_contact_emails`.`contact_id` IN (/* 42000+ different IDs in here */);
SHOW CREATE TABLE
CREATE TABLE `sales_contact_emails` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`contact_id` int(11) NOT NULL,
`email_id` int(11) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `contact_id` (`contact_id`,`email_id`),
KEY `email_id_refs_id_5cebc930` (`email_id`)
) ENGINE=InnoDB AUTO_INCREMENT=26433420 DEFAULT CHARSET=latin1;
CREATE TABLE `sales_email` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`type` varchar(1) NOT NULL,
`email` varchar(75) NOT NULL,
PRIMARY KEY (`id`),
KEY `sales_email_idx_email` (`email`)
) ENGINE=InnoDB AUTO_INCREMENT=189951028 DEFAULT CHARSET=latin1;
Explain MySQL 5.6
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | sales_contact_emails | range | contact_id,email_id_refs_id_5cebc930 | contact_id | 4 | NULL | 41376 | Using where; Using index |
| 1 | SIMPLE | sales_email | eq_ref | PRIMARY | PRIMARY | 4 | company.sales_contact_emails.email_id | 1 | NULL |
Profile (First run after reboot):
| Status | Duration |
|---|---|
| starting | 0.011993 |
| checking permissions | 0.000010 |
| checking permissions | 0.000006 |
| Opening tables | 0.006008 |
| init | 0.005540 |
| System lock | 0.000013 |
| optimizing | 0.001441 |
| statistics | 0.042569 |
| preparing | 0.001433 |
| executing | 0.000008 |
| Sending data | 0.051343 |
| end | 0.000009 |
| query end | 0.000007 |
| closing tables | 0.000010 |
| freeing items | 0.001068 |
| cleaning up | 0.000417 |
Explain MySQL 5.7
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | sales_contact_emails | NULL | index | contact_id,email_id_refs_id_5cebc930 | contact_id | 8 | NULL | 24978777 | 50 | Using where; Using index |
| 1 | SIMPLE | sales_email | NULL | eq_ref | PRIMARY | PRIMARY | 4 | company.sales_contact_emails.email_id | 1 | 100 | NULL |
Profile (First run after boot):
| Status | Duration |
|---|---|
| starting | 0.015114 |
| checking permissions | 0.000014 |
| checking permissions | 0.000004 |
| Opening tables | 0.000019 |
| init | 0.007370 |
| System lock | 0.000023 |
| optimizing | 0.001555 |
| statistics | 0.024513 |
| preparing | 0.001390 |
| executing | 0.000006 |
| Sending data | 148.873194 |
| end | 0.000012 |
| query end | 0.000010 |
| closing tables | 0.000008 |
| freeing items | 0.001544 |
| cleaning up | 0.000711 |
Profile (Second run)
| Status | Duration |
|---|---|
| starting | 0.014875 |
| checking permissions | 0.000012 |
| checking permissions | 0.000005 |
| Opening tables | 0.000019 |
| init | 0.006042 |
| System lock | 0.000015 |
| optimizing | 0.001538 |
| statistics | 0.023283 |
| preparing | 0.001399 |
| executing | 0.000007 |
| Sending data | 6.101950 |
| end | 0.000013 |
| query end | 0.000009 |
| closing tables | 0.000008 |
| freeing items | 0.000996 |
| cleaning up | 0.000438 |
Facts
- About 25 million rows in the
sales_contact_emailstable. contact_idindex is a compound index (contact_id, email_id) with extremely high cardinality. It's effectively an unique index.SHOW INDEXESbelow:
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type |
|---|---|---|---|---|---|---|---|---|---|---|
| sales_contact_emails | 0 | contact_id | 1 | contact_id | A | 24976228 | NULL | NULL | BTREE | |
| sales_contact_emails | 0 | contact_id | 2 | email_id | A | 24601066 | NULL | NULL | BTREE |
- The minimum id value for the IN CLAUSE is
43890973and the highest value is43839266(difference of about 51k). Sweet spot for arange scanI suppose. This matches (kind of) the estimation for the MySQL 5.6 explain plain. - MySQL Versions:
5.6.44vs5.7.33 - The difference between the two explain plans is the
type=range vs type=indexand the huge estimation forrows. - Using RDS for both examples. Same snapshot (one is migrated to MySQL 5.7 of course).
- Using default Parameters/Options Groups for both engines.
- 0 traffic, only myself and my query benchmark.
- The actual SQL statement is about 400KB.
Research so Far
- https://bugs.mysql.com/bug.php?id=87164: It seems that there's a bug with IN CLAUSES on MySQL 5.7, but this may not be related to my specifc case. I tried the same snapshot for MySQL 8 and got the same performance from 5.7
- tried to disable/enable all @@optimizer_switch (full off and full on)
- tried to play with
@@eq_range_index_dive_limit(50k instead 200 and no difference)
Questions
- What behavior changed on MySQL 5.7 that made those kind of queries so slow?
- Is there any way to change the behaviour back to the old way to get similar performance with none or minimal change in the query structure?
Focus
I know that an IN CLAUSE with 42k itens may be a problem itself, and there are workarounds for that (subqueries, joins, temporary tables...), but that's not the question at the momment. The focus is MySQL 5.7 vs 5.6 engine/optimizer/query-planner different behaviours.
Thanks in advance, :)