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 issueUSE 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
Best Answer
(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-byteENUM
or normalized to a 1-byteTINYINT UNSIGNED
(1 byte) instead ofVARCHAR(25)
?subtotal
decimal(25,2) takes 11 bytes. If you don't need the precision ofDECIMAL
, consider an 8-byteDOUBLE
. Are there more than 16M sites? If not, consider a 3-byteMEDIUMINT UNSIGNED
instead of a 4-byteINT SIGNED
. Etc.In place of
USE INDEX..
this might be safer:IGNORE INDEX(PRIMARY)
.