Query for a month works faster then for a week

Question

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 >= '2016-06-01 07:00:00') AND 
   (t1.created_at <= '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

Answer 1

(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).