Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Not a Bug
-
10.4.4
-
None
Description
From: https://dba.stackexchange.com/questions/238264/mariadb-10-4-4-exists-problem
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 |
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` |
+------+--------------+-----------+-------+--------------------------+----------+---------+-------+---------+--------------------------+ |
| 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 |----------+----------+---------+-------+---------+--------------------------+ |
group_id isn't part of the query however it was the key chosen for the query.