Same query with the same data, different performance

Question

I'm running a SQL query with the same set of data on 2 different servers.

Server 1: mysql Ver 14.14 Distrib 5.1.73, for unknown-linux-gnu (x86_64) using readline 5.1

Server 2: mysql Ver 15.1 Distrib 10.0.12-MariaDB, for osx10.10 (x86_64) using readline 5.1

The query is:

SELECT
 *
FROM
 `user`
WHERE
 user_id IN (SELECT user_id FROM reader_detail)
AND `user`.type <> 4
AND `user`.type <> 11
AND user_id NOT IN (
 SELECT
  `user_id`
 FROM
  reader_log
 WHERE
  reader_log.`date` > DATE_SUB(CURDATE(), INTERVAL 6 MONTH)
 GROUP BY
  user_id
 ORDER BY
  user_id ASC
)
AND username IS NOT NULL;

EXPLAIN SELECT on Server 1:

+----+--------------------+---------------+-----------------+---------------+---------+---------+------+-------+--------------------------+
| id | select_type        | table         | type            | possible_keys | key     | key_len | ref  | rows  | Extra                    |
+----+--------------------+---------------+-----------------+---------------+---------+---------+------+-------+--------------------------+
|  1 | PRIMARY            | user          | ALL             | NULL          | NULL    | NULL    | NULL | 29865 | Using where              |
|  3 | DEPENDENT SUBQUERY | reader_log    | index           | NULL          | PRIMARY | 7       | NULL |    17 | Using where; Using index |
|  2 | DEPENDENT SUBQUERY | reader_detail | unique_subquery | PRIMARY       | PRIMARY | 4       | func |     1 | Using index              |
+----+--------------------+---------------+-----------------+---------------+---------+---------+------+-------+--------------------------+

EXPLAIN SELECT on Server 2:

+------+--------------------+---------------+----------------+----------------------+---------+---------+----------------------------------------+-------+--------------------------+
| id   | select_type        | table         | type           | possible_keys        | key     | key_len | ref                                    | rows  | Extra                    |
+------+--------------------+---------------+----------------+----------------------+---------+---------+----------------------------------------+-------+--------------------------+
|    1 | PRIMARY            | reader_detail | index          | PRIMARY              | PRIMARY | 4       | NULL                                   | 17682 | Using where; Using index |
|    1 | PRIMARY            | user          | eq_ref         | PRIMARY              | PRIMARY | 4       | rklocaldbmigrate.reader_detail.user_id |     1 | Using where              |
|    3 | DEPENDENT SUBQUERY | reader_log    | index_subquery | PRIMARY,user_id_date | PRIMARY | 4       | func                                   |    16 | Using index; Using where |
+------+--------------------+---------------+----------------+----------------------+---------+---------+----------------------------------------+-------+--------------------------+

The query takes less than 1 second to run on server 2: 5894 rows in set (0.09 sec) However, I had to abort on server 1 after the query ran for over 1 minute.

What's happening here? Is it because server 2 is using MariaDB whereas it's MySQL on server 1? I'm clueless here. Again, it's the same set of data (I ran a mysqldump from server 1 to server 2).

Raisen Raisen · Accepted Answer · 2015-04-07T11:39:55

0

votes

The solution was to replace MySQL with MariaDB on server 1.

Same query with the same data, different performance

1 Answers