`t2.t1_id IN (select t1.id from t1 ...)` is slower then `t2.t1_id IN (1,2,3,...,150)`

Question

Query with subquery is much slower:

SELECT COUNT(*)
FROM `t2`
WHERE `t2`.`t1_id` IN
    (SELECT `t1`.`id`
     FROM `t1`
     WHERE `t1`.`site_id` = 5978)
  AND (t2.created_at >= '2016-02-13 08:00:00')
  AND (t2.created_at <= '2016-02-14 07:59:59');

returns 34038 in 9 seconds

subquery

SELECT `t1`.`id` FROM `t1` WHERE `t1`.`site_id` = 5978

returns 150 ids like: 1, 2, 3, ... 150

without subquery:

SELECT COUNT(*)
FROM `t2`
WHERE `t2`.`t1_id` IN (1, 2, 3, ..., 150)
  AND (t2.created_at >= '2016-02-13 08:00:00')
  AND (t2.created_at <= '2016-02-14 07:59:59');

returns 34038 in 0.1 seconds

EXPLAIN for query with subquery:

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: NULL
         type: ref
possible_keys: PRIMARY,index_t1_on_site_id_and_created_at
          key: index_t1_on_site_id_and_created_at
      key_len: 4
          ref: const
         rows: 158
     filtered: 100.00
        Extra: Using index
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: t2
   partitions: NULL
         type: ref
possible_keys: ...,index_t2_on_t1_id_and_and_created_at,...
          key: index_t2_on_t1_id_and_and_created_at
      key_len: 4
          ref: production.t1.id
         rows: 622
     filtered: 11.11
        Extra: Using where; Using index

EXPLAIN for query without subquery:

  *************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t2
   partitions: NULL
         type: range
possible_keys: ...,index_t2_on_t1_id_and_and_created_at,...
          key: index_t2_on_t1_id_and_and_created_at
      key_len: 9
          ref: NULL
         rows: 68239
     filtered: 100.00
        Extra: Using where; Using index

Tables:

CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `site_id` int(11) NOT NULL,
...  
  PRIMARY KEY (`id`),
  KEY `index_t1_on_site_id_and_created_at` (`site_id`,`created_at`)
)

CREATE TABLE `t2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `t1_id` int(11) NOT NULL,
  `created_at` datetime NOT NULL,
...
  PRIMARY KEY (`id`),
...  
  KEY `index_t2_on_t1_id_and_type_and_created_at` (`t1_id`,`created_at`),
)

MySQL 5.7.11 (the same behaviour in 5.6.23)

Is there any way to make the first query faster?

Answer 1

This should be best:

SELECT  COUNT(*)
    FROM  t1
    JOIN  `t2`  ON t2.t1_id = t1.id
    WHERE  `t1`.`site_id` = 5978)
      AND  (t2.created_at >= '2016-02-13 08:00:00')
      AND  (t2.created_at  < '2016-02-13 08:00:00' + INTERVAL 24 HOUR);

with these indexes, with the columns in the order given:

t1: INDEX(site_id, id)
t2: INDEX(created_at, t1_id)

Caveat: If t2.t1_id = t1.id is "many:1", you will get the right answer. If it is "many:many", you will get an inflated COUNT.

This formulation may also be efficient:

SELECT  COUNT(*)
    FROM  `t2`
    WHERE  EXISTS   ( SELECT  *
              FROM  t1
              WHERE  `id` = t2.t1_id
                AND  `site_id` = 5978 
                    )
      AND  (t2.created_at >= '2016-02-13 08:00:00')
      AND  (t2.created_at  < '2016-02-13 08:00:00' + INTERVAL 24 HOUR);

with these index, with the columns in the order given:

t2: INDEX(created_at, t1_id)