MariaDB 10.4.4 "exists" problem

by Azrael   Last Updated May 16, 2019 01:06 AM

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 |
+------+--------------+-----------+-------+--------------------------+---------+---------+------+---------+--------------------------+

Anyone willing to explain me why separate exists is faster rather than one?

Engine is InnoDB

Tags : mariadb


Related Questions


Updated October 24, 2018 17:06 PM

Updated August 17, 2017 10:06 AM

Updated May 08, 2019 09:06 AM

Updated October 11, 2016 09:02 AM

Updated May 03, 2017 19:06 PM