0

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

  • 1
    "Everything is indexed", isn't quite enough information. Can you be explicit with SHOW CREATE TABLE accounts / SHOW CREATE TABLE table1. EXPLAIN {query} for both queries would be useful too. Edit the question to include this please. – danblack May 16 at 0:26
  • @danblack I updated a question for you. – Azrael May 16 at 0:38
  • Are you getting the right and same results from both queries? The horrible/wrong thing is in the explain of query 1, it says that group_id is used as a key, when its not part of the query. This looks like a bug - please report it jira.mariadb.org – danblack May 16 at 1:04
  • Improving query 2 - bit of a guess, but try: change table1_order_id_index to be order_id, member_id and member_id_2 to be member_id,order_id. Both fields are needed in the query. You won't get the performance of query 1, but I suspect that's quite wrong. – danblack May 16 at 1:16
  • @danblack well, exactly the same results. keep in mind 1st one is like 1500x faster, even though it’s using group_id as a key... this index did not change anything unfortunately – Azrael May 16 at 1:17
0
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.

  • Query cache is off. – Azrael May 18 at 7:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.