`t2.t1_id IN (select t1.id from t1 ...)` is slower then `t2.t1_id IN (1,2,3,...,150)`
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 &gt;= '2016-02-13 08:00:00')
      AND  (t2.created_at  &lt; '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 &gt;= '2016-02-13 08:00:00')
      AND  (t2.created_at  &lt; '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)