I have 2 similar queries:
1st with 0.002s execution time
SELECT *
FROM `table1`
WHERE (`order_id` = 1234
OR (EXISTS
(SELECT *
FROM `accounts`
WHERE `table1`.`member_id` = `accounts`.`id` )
AND EXISTS
(SELECT *
FROM `accounts`
WHERE `accounts`.`name` = 'test' )))
ORDER BY `table1`.`id`
and 2nd with 2.5s execution time
SELECT *
FROM `table1`
WHERE (`order_id` = 1234
OR EXISTS
(SELECT *
FROM `accounts`
WHERE `table1`.`member_id` = `accounts`.`id`
AND (`accounts`.`name` = 'test')))
ORDER BY `table1`.`id`
Table1 is around 2mln records, accounts is around 30k
Everything is indexed.
CREATE TABLE `accounts` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`group_id` int(11) NOT NULL DEFAULT 2,
PRIMARY KEY (`id`),
KEY `name` (`name`),
KEY `group_id` (`group_id`),
) ENGINE=InnoDB AUTO_INCREMENT=33261 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
CREATE TABLE `table1` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`order_id` int(11) NOT NULL,
`order_type` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`member_id` int(11) NOT NULL,
`date` int(11) NOT NULL,
`created_at` timestamp NOT NULL DEFAULT current_timestamp(),
`updated_at` timestamp NOT NULL DEFAULT current_timestamp(),
PRIMARY KEY (`id`),
KEY `table1_order_id_index` (`order_id`),
KEY `member_id_2` (`member_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1985597 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
explain1:
+------+--------------+-----------+-------+--------------------------+----------+---------+-------+---------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+--------------+-----------+-------+--------------------------+----------+---------+-------+---------+--------------------------+
| 1 | PRIMARY | table1 | index | table1_order_id_index | PRIMARY | 4 | NULL | 1965346 | Using where |
| 3 | SUBQUERY | accounts | ref | name | name | 767 | const | 1 | Using where; Using index |
| 2 | MATERIALIZED | accounts | index | PRIMARY,id | group_id | 4 | NULL | 32558 | Using index |
+------+--------------+-----------+-------+--------------------------+----------+---------+-------+---------+--------------------------+
explain2:
+------+--------------+-----------+-------+--------------------------+---------+---------+------+---------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+--------------+-----------+-------+--------------------------+---------+---------+------+---------+--------------------------+
| 1 | PRIMARY | table1 | index | table1_order_id_index | PRIMARY | 4 | NULL | 1965348 | Using where |
| 2 | MATERIALIZED | accounts | range | PRIMARY,id,name | name | 767 | NULL | 1 | Using where; Using index |
+------+--------------+-----------+-------+--------------------------+---------+---------+------+---------+--------------------------+
MariaDB 10.1 (downgraded, query 2), -0.5s!
+------+--------------+-----------+-------+--------------------------+---------+---------+-------+---------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+--------------+-----------+-------+--------------------------+---------+---------+-------+---------+--------------------------+
| 1 | PRIMARY | table1 | index | table1_order_id_index | PRIMARY | 4 | NULL | 1974108 | Using where |
| 2 | MATERIALIZED | accounts | ref | PRIMARY,id_2,id,name | id_2 | 767 | const | 1 | Using where; Using index |
+------+--------------+-----------+-------+--------------------------+---------+---------+-------+---------+--------------------------+
Anyone willing to explain me why separate exists is faster rather than one?
Engine is InnoDB
Best Answer
probably turned into
TRUE
during theEXPLAIN
. It would be a trivial lookup in accounts to check that some row had name='test'.Rerun the first query with
SELECT SQL_NO_CACHE ...
to be sure that it is not the Query cache tricking you.