I'm running MySQL 5.7.15 on Ubuntu server 16.04.
After I've partitioned a table by range, select queries for some values are slow. I've checked that queries are slow if key is missing in older partition.
CREATE TABLE `table` (
`id` bigint(20) unsigned NOT NULL,
`clock` int(11) NOT NULL DEFAULT '0',
`value` bigint(20) unsigned NOT NULL DEFAULT '0',
KEY `table_1` (`id`,`clock`),
KEY `clock` (`clock`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (clock)
(PARTITION p2016_10_16 VALUES LESS THAN (1476655200) ENGINE = InnoDB,
PARTITION p2016_10_17 VALUES LESS THAN (1476741600) ENGINE = InnoDB,
PARTITION p2016_10_18 VALUES LESS THAN (1476828000) ENGINE = InnoDB,
PARTITION p2016_10_19 VALUES LESS THAN (1476914400) ENGINE = InnoDB,
PARTITION p2016_10_20 VALUES LESS THAN (1477000800) ENGINE = InnoDB,
PARTITION p2016_10_21 VALUES LESS THAN (1477087200) ENGINE = InnoDB) */|
Executing the query:
select *
from table
where id='25885'
and clock>1476044179
order by clock desc
limit 3;
...takes over 1 second, maybe because there are only entries (15 tuples) in the last partition.
If I instead execute:
select *
from table
where id='24946'
and clock>1476044179
order by clock desc
limit 3;
...the query is faster (much faster) maybe because there are entries in all partitions (8k tuples).
What am I missing?
PRIMARY KEY?? Not
AUTO_INCREMENT`??