Query for a month works faster then for a week I have strange situation when query for a month works faster then for a week:

There is a request for a month (2016-06-01 - 2016-07-08) that works quickly and uses proper index:

SELECT  `t1`.* FROM `t1` 
  INNER JOIN `t2` ON `t2`.`t1_id` = `t1`.`id` 
  WHERE
   `t1`.`type` IN ... AND 
   `t1`.`site_id` = ... AND 
   (t1.created_at &gt;= '2016-06-01 07:00:00') AND 
   (t1.created_at &lt;= '2016-07-08 06:59:59')  
   ORDER BY `t1`.`id` DESC LIMIT 30 OFFSET 0;


returns 48 rows in 85ms

it uses INDEX: site_id, type, created_at, subtotal

The same request for a week (2016-07-01 - 2016-07-08 or 2016-06-01 - 2016-06-08) work in 1000 times slower and does not use index

returns 7 rows in 420 sec
returns 13 rows in 420 sec

Table has ~170M records


MySQL 5.6.23 
analyze table t1, t2 has not fixed the issue
USE INDEX(...) solves the issue, but it's not a solution for me, I'can't manage indexes manually instead of MySQL
if remove LIMIT 30 OFFSET 0 it works fast, but I need it for pagination
ORDER BY t1.created_at works faster, but not enough - 1-10 sec. It also uses wrong index


Is there any solutions?

UPDATE:

CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `site_id` int(11) NOT NULL,
  `subtotal` decimal(25,2) NOT NULL,
  `created_at` datetime DEFAULT NULL,
  `type` varchar(25) DEFAULT NULL,
...
) ENGINE=InnoDB DEFAULT CHARSET=utf8


Update 2:

There is explain for a week (slow query):

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
         type: index
possible_keys: PRIMARY,index_t1_on_site_id_and_c1_and_type_and_c2,index_t1_on_created_at_and_site_id,index_t1_on_type,index_t1_on_site_id_and_type_and_created_at_and_subtotal,index_t1_on_site_and_type_and_c3_and_created_at
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 8991557
        Extra: Using where
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: t2
         type: eq_ref
possible_keys: i1,i2
          key: i1
      key_len: 4
          ref: production.t1.id
         rows: 1
        Extra: Using index


There is explain for a month (fast query):

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
         type: range
possible_keys: PRIMARY,index_t1_on_site_id_and_c1_and_type_and_c2,index_t1_on_created_at_and_site_id,index_t1_on_type,index_t1_on_site_id_and_type_and_created_at_and_subtotal,index_t1_on_site_and_type_and_c3_and_created_at
          key: index_t1_on_site_id_and_type_and_created_at_and_subtotal
      key_len: 88
          ref: NULL
         rows: 7709
        Extra: Using index condition; Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: t2
         type: eq_ref
possible_keys: i1,i2
          key: i1
      key_len: 4
          ref: production.t1.id
         rows: 1
        Extra: Using index
(Some of this is redundant, but I will spell it out anyway.)

I think 5.6.23, with MRR, is smart enough to use all 3 columns of INDEX(site_id, type, created_at) in that order.  (Older versions would not work as well.)

If you are using a 'range' of created_at values, nothing after that column will be used in optimization.  (Unless it is a 'covering' index.)

Instead of using OFFSET, "remember where you left off":  pagination blog.

With 170M rows, you should consider tightening up the datatypes wherever possible.  For example, can type changed to a 1-byte ENUM or normalized to a 1-byte TINYINT UNSIGNED (1 byte) instead of VARCHAR(25)?  subtotal decimal(25,2) takes 11 bytes.  If you don't need the precision of DECIMAL, consider an 8-byte DOUBLE.  Are there more than 16M sites?  If not, consider a 3-byte MEDIUMINT UNSIGNED instead of a 4-byte INT SIGNED.  Etc.

In place of USE INDEX.. this might be safer: IGNORE INDEX(PRIMARY).