mysql> EXPLAIN SELECT * from `picks` WHERE `match_id` in (SELECT `match_id` from `picks` WHERE `match_id` in (SELECT `id` FROM `matches` WHERE date >= '2014-01-27' AND date < '2999-01-01') AND `hero_id` = 1 AND `type` = 'pick' AND `team` = 0 GROUP BY `match_id`) AND `type` = 'pick' AND `team` = 1
    ->
    -> ;
+----+--------------------+---------+-----------------+--------------------------+--------------+---------+-------+------+-----------------------------------------------------------------------------+
| id | select_type        | table   | type            | possible_keys            | key          | key_len | ref   | rows | Extra                                                                       |
+----+--------------------+---------+-----------------+--------------------------+--------------+---------+-------+------+-----------------------------------------------------------------------------+
|  1 | PRIMARY            | picks   | ref             | type,team,type_2         | type         | 6       | const | 3999 | Using where                                                                 |
|  2 | DEPENDENT SUBQUERY | picks   | index_merge     | type,team,type_2,hero_id | hero_id,team | 4,4     | NULL  |    6 | Using intersect(hero_id,team); Using where; Using temporary; Using filesort |
|  3 | DEPENDENT SUBQUERY | matches | unique_subquery | PRIMARY                  | PRIMARY      | 4       | func  |    1 | Using where                                                                 |
+----+--------------------+---------+-----------------+--------------------------+--------------+---------+-------+------+-----------------------------------------------------------------------------+
3 rows in set (0.00 sec)



mysql> SHOW CREATE TABLE picks;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                    |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| picks | CREATE TABLE `picks` (
  `match_id` int(11) NOT NULL,
  `hero_id` int(3) NOT NULL,
  `order` int(2) NOT NULL,
  `type` varchar(4) NOT NULL,
  `team` int(1) NOT NULL,
  UNIQUE KEY `match_id` (`match_id`,`order`),
  KEY `match_id_2` (`match_id`),
  KEY `type` (`type`),
  KEY `team` (`team`),
  KEY `type_2` (`type`),
  KEY `hero_id` (`hero_id`),
  KEY `match_id_3` (`match_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)