2

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
  • is t1.created_at column a date or timestamp data type? if it is a char or varchar2 field, then that could be confusing the optimizer. – Mark Stewart Jul 8 '16 at 18:26
  • t1.created_at is datetime – Alexey Jul 8 '16 at 18:32
  • Why is the join to t2 needed? – ypercubeᵀᴹ Jul 8 '16 at 19:16
  • 2
    @Alexey index (site_id, created_at, type) might work better (only one range scan can be used efficiently and IN is a range scan too). Or maybe just ORDER BY t1.id + 1 to disable the order by .. limit optimization if it is making troubles. – jkavalik Jul 8 '16 at 20:46
  • 1
    @Alexey there is an "optimization" for cases when you only get first N rows and optimizer decides to go over the PRIMARY key expecting to find N matching rows fast but being wrong and searching much more. The id + 1 (or + 0, but for ORDER BY it behaves the same) is just a trick to disallow that and confirm it as a cause. There may be much better index possible which won't need such hacks, maybe (site_id, id, created_at, type) might work well, removing filesort and utilizing Using index condition in both cases but its effectivity depends on your data distribution so you need to test. – jkavalik Jul 8 '16 at 21:45
2

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

  • 5.6.23 is smart and uses right index most time, but sometime it does and it strange. I found that request works properly in MySQL 5.7.11, I will update DB soon – Alexey Jul 14 '16 at 21:29
  • IGNORE INDEX(PRIMARY) - works. Thanks for one more hack :) – Alexey Jul 14 '16 at 21:34
  • I tried to minimize rows length (VARCHAR -> ENUM), but I haven't got viewable performance increase on test server, so I can't push it on production without good reason – Alexey Jul 14 '16 at 21:54
  • 1
    When the data becomes bigger than the buffer_pool, performance is likely to decline visibly. (Until then, shrinking datatypes is unlikely to show much difference.) – Rick James Jul 15 '16 at 2:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.