I have a query with multiple EXISTS
statements and I don't understand why it becomes exponentially slow after about 7 EXISTS
.
SELECT `products`.`id`
FROM `products`
WHERE EXISTS(SELECT `vehicles`.`id` FROM `vehicles`) #1 ~30ms
AND EXISTS(SELECT `vehicles`.`id` FROM `vehicles`) #2 ~30ms
AND EXISTS(SELECT `vehicles`.`id` FROM `vehicles`) #3 ~30ms
AND EXISTS(SELECT `vehicles`.`id` FROM `vehicles`) #4 ~35ms
AND EXISTS(SELECT `vehicles`.`id` FROM `vehicles`) #5 ~35ms
AND EXISTS(SELECT `vehicles`.`id` FROM `vehicles`) #6 ~35ms
AND EXISTS(SELECT `vehicles`.`id` FROM `vehicles`) #7 ~40ms
AND EXISTS(SELECT `vehicles`.`id` FROM `vehicles`) #8 ~100ms
AND EXISTS(SELECT `vehicles`.`id` FROM `vehicles`) #9 ~500ms
AND EXISTS(SELECT `vehicles`.`id` FROM `vehicles`) #10 ~4800ms
AND EXISTS(SELECT `vehicles`.`id` FROM `vehicles`) #11 ~52500ms
As you can see, the first 7th EXISTS
are pretty fast... then the performance goes down very quickly.
products
and vehicles
are pretty simple tables, with 1000 and 5000 rows respectively.
Here is the explain statement:
+--+-----------+--------+----------+-----+-------------+-------+-------+----+----+--------+----------------------------------------------------------------+
|id|select_type|table |partitions|type |possible_keys|key |key_len|ref |rows|filtered|Extra |
+--+-----------+--------+----------+-----+-------------+-------+-------+----+----+--------+----------------------------------------------------------------+
|1 |SIMPLE |vehicles|NULL |index|NULL |PRIMARY|8 |NULL|5266|100 |Using index; FirstMatch |
|1 |SIMPLE |vehicles|NULL |index|NULL |PRIMARY|8 |NULL|5266|100 |Using index; FirstMatch(vehicles); Using join buffer (hash join)|
|1 |SIMPLE |vehicles|NULL |index|NULL |PRIMARY|8 |NULL|5266|100 |Using index; FirstMatch(vehicles); Using join buffer (hash join)|
|1 |SIMPLE |vehicles|NULL |index|NULL |PRIMARY|8 |NULL|5266|100 |Using index; FirstMatch(vehicles); Using join buffer (hash join)|
|1 |SIMPLE |vehicles|NULL |index|NULL |PRIMARY|8 |NULL|5266|100 |Using index; FirstMatch(vehicles); Using join buffer (hash join)|
|1 |SIMPLE |vehicles|NULL |index|NULL |PRIMARY|8 |NULL|5266|100 |Using index; FirstMatch(vehicles); Using join buffer (hash join)|
|1 |SIMPLE |vehicles|NULL |index|NULL |PRIMARY|8 |NULL|5266|100 |Using index; FirstMatch(vehicles); Using join buffer (hash join)|
|1 |SIMPLE |vehicles|NULL |index|NULL |PRIMARY|8 |NULL|5266|100 |Using index; FirstMatch(vehicles); Using join buffer (hash join)|
|1 |SIMPLE |vehicles|NULL |index|NULL |PRIMARY|8 |NULL|5266|100 |Using index; FirstMatch(vehicles); Using join buffer (hash join)|
|1 |SIMPLE |vehicles|NULL |index|NULL |PRIMARY|8 |NULL|5266|100 |Using index; FirstMatch(vehicles); Using join buffer (hash join)|
|1 |SIMPLE |vehicles|NULL |index|NULL |PRIMARY|8 |NULL|5266|100 |Using index; FirstMatch(vehicles); Using join buffer (hash join)|
|1 |SIMPLE |products|NULL |index|NULL |PRIMARY|8 |NULL|959 |100 |Using index; Using join buffer (hash join) |
+--+-----------+--------+----------+-----+-------------+-------+-------+----+----+--------+----------------------------------------------------------------+
And the table definitions:
Products:
+-----+---------------+----+---+-------+--------------+
|Field|Type |Null|Key|Default|Extra |
+-----+---------------+----+---+-------+--------------+
|id |bigint unsigned|NO |PRI|NULL |auto_increment|
|name |varchar(255) |NO | |NULL | |
+-----+---------------+----+---+-------+--------------+
Vehicles:
+-----+---------------+----+---+-------+--------------+
|Field|Type |Null|Key|Default|Extra |
+-----+---------------+----+---+-------+--------------+
|id |bigint unsigned|NO |PRI|NULL |auto_increment|
|name |varchar(255) |NO | |NULL | |
+-----+---------------+----+---+-------+--------------+
The MySQL version is 8.0.23.
This is a working fiddle reproducing the problem https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=e0d671779e1a8335dd585508cc657429
I know this query doesn't make any sense but it is a strange behavior I only observe in MySQL 8.0 (in the fiddle you can switch the MySQL version to < 8.0, or use MariaDB, and the performances will be ok).
This is all about curiosity, understanding why it behaves like this, with this particular query.
Do you have any idea about what can cause this ?