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

  • I did few simple tests and discovered that the same config on other, better machine, on mariadb 10.1 let that query run in 0.7s, while on mariadb 10.3.3 - 1.2s +0.5s because I switched version from 10.1 to 10.3.3 ? – Azrael May 18 at 7:59
  • The separate exists means that the queries are different and may return different results. It's no surprise that leads to different execution plans and different performance. – ypercubeᵀᴹ May 18 at 15:13
  • I know, thank you for answers. I just got to know that there is huge performance difference between 10.1 and 10.3+ mariadb -> 10.1 is faster. – Azrael May 18 at 19:45
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.