CODEWITHSUNDEEP
mysqlindexing
Werner
Werner

Reputation: 1847

MySql: Optimizing Subquery with index_merge

I want to lists all users and the status of the newsletter-subscribtion. Since someone doesn't need to be a user if subscribed to the newsletter, I'm doing something like:

SELECT user.id, user.email,
(SELECT newsletter.status FROM newsletter 
      WHERE newsletter.email=user.email OR newsletter.user = user.id) AS status
FROM user
WHERE...

Indexes are user.id, user.email, newsletter.email, newsletter.user.

The OR makes the query incredible slow. I found here Union as sub query MySQL that you can do an "index merge" which will speed-up the query. But I'm not sure how to force MySQL to do an index merge in my case. Any ideas?

Added: 2nd row of explain doesn't use a key.

DROP TABLE if exists user;
DROP TABLE if exists newsletter;

create table user (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
`email` varchar(255) DEFAULT NULL, INDEX email(email)
) ENGINE=InnoDB;

create table newsletter (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
`status` enum('subscribed','unsubscribed') DEFAULT NULL,
`email` varchar(255) DEFAULT NULL, INDEX email(email),
`user` int(10) unsigned DEFAULT NULL, INDEX user(user)
) ENGINE=InnoDB;

EXPLAIN SELECT user.id, user.email,
(SELECT newsletter.status FROM newsletter 
      WHERE newsletter.email=user.email OR newsletter.user = user.id) AS status
FROM user;

+----+--------------------+------------+------------+-------+---------------+-------+---------+------+------+----------+------------------------------------------------+
| id | select_type        | table      | partitions | type  | possible_keys | key   | key_len | ref  | rows | filtered | Extra                                          |
+----+--------------------+------------+------------+-------+---------------+-------+---------+------+------+----------+------------------------------------------------+
|  1 | PRIMARY            | user       | NULL       | index | NULL          | email | 768     | NULL |    1 |   100.00 | Using index                                    |
|  2 | DEPENDENT SUBQUERY | newsletter | NULL       | ALL   | email,user    | NULL  | NULL    | NULL |    1 |   100.00 | Range checked for each record (index map: 0x6) |
+----+--------------------+------------+------------+-------+---------------+-------+---------+------+------+----------+------------------------------------------------+

EXPLAIN SELECT user.id, user.email, newsletter.status
FROM user
LEFT JOIN newsletter ON (newsletter.email=user.email OR newsletter.user = user.id);

+----+-------------+------------+------------+-------+---------------+-------+---------+------+------+----------+------------------------------------------------+
| id | select_type | table      | partitions | type  | possible_keys | key   | key_len | ref  | rows | filtered | Extra                                          |
+----+-------------+------------+------------+-------+---------------+-------+---------+------+------+----------+------------------------------------------------+
|  1 | SIMPLE      | user       | NULL       | index | NULL          | email | 768     | NULL |    1 |   100.00 | Using index                                    |
|  1 | SIMPLE      | newsletter | NULL       | ALL   | email,user    | NULL  | NULL    | NULL |    1 |   100.00 | Range checked for each record (index map: 0x6) |
+----+-------------+------------+------------+-------+---------------+-------+---------+------+------+----------+------------------------------------------------+

Upvotes: 1

Views: 634

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562891

Unless you have switched off index_merge, MySQL will use it if it thinks it will be of benefit. But I find it kicks in less often than one might expect, and even when it does, it's not very helpful — the query is still a lot slower than using an index in the conventional way.

The typical solution for these types of queries is to do a UNION of two simpler queries.

explain select u.id, u.email, n.status
 from user u left join newsletter n on u.email=n.email
 union
 select u.id, u.email, n.status
 from user u left join newsletter n on u.id=n.user;

+----+--------------+------------+-------+---------------+-------+---------+--------------+------+-----------------+
| id | select_type  | table      | type  | possible_keys | key   | key_len | ref          | rows | Extra           |
+----+--------------+------------+-------+---------------+-------+---------+--------------+------+-----------------+
|  1 | PRIMARY      | u          | index | NULL          | email | 403     | NULL         |    1 | Using index     |
|  1 | PRIMARY      | n          | ref   | email         | email | 403     | test.u.email |    1 | NULL            |
|  2 | UNION        | u          | index | NULL          | email | 403     | NULL         |    1 | Using index     |
|  2 | UNION        | n          | ref   | user          | user  | 5       | test.u.id    |    1 | NULL            |
| NULL | UNION RESULT | <union1,2> | ALL   | NULL          | NULL  | NULL    | NULL         | NULL | Using temporary |
+----+--------------+------------+-------+---------------+-------+---------+--------------+------+-----------------+

You'd be better off fixing your data so you only have to join on the user id, not on the email.

Upvotes: 1

Related Questions