MariaDB 10.4.4 “exists” problem

mariadb

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

EXISTS
         (SELECT *
          FROM `accounts`
          WHERE `accounts`.`name` = 'test' )))

probably turned into TRUE during the EXPLAIN. 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.