Search is slow in a partitioned table

Question

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?

Answer 1

You seem to have an issue with key distribution with id and the Query Optimizer.

Since the id is not being factored into the partitioning scheme, I only see MySQL having to look up the id up to 6 times. Depending on when and how frequent each id value is written, the query could be using the wrong index.

There are two things you could do to see if id key distribution is at issue

SUGGESTION #1 : Run EXPLAIN on each query

explain select *
from table
where id='25885'
and clock>1476044179
order by clock desc
limit 3;
explain select *
from table
where id='24946'
and clock>1476044179
order by clock desc
limit 3;

The output will tell you what index was chosen to scan for rows. If the clock index is being used, then the clock is being scanned in the index, but the id is being scanned from the table.

SUGGESTION #2 : Change the ORDER BY

If the order by clock desc is making the Query Optimizer choose the clock index, try this:

order by id,clock desc

That way, the scan will be from table_1 index, starting from the last time anid was inserted.

GIVE IT A TRY !!!

Answer 2

Your partition is on the basis of clock.. The value of clock you have provided lies in the first partition .. so it will search from first partition to the last partition... I.e. it will go through all the partitions to get the value..

If Id is auto increment primary key of the database.. then as id in second query is smaller than the one in first query.. it finds it earlier.. where as the more partitions apart the id is, the more the time taken will be.

Your queries will be much faster if you partition on id and you don't always have to go through each index..

Partition is not playing part for faster query execution because you have specified the constraint which is making it go through all partitions.. if you would have given a clock value which is in 4th or 5th partition.. it would have searched the 4th or 5th partition onward partitions.. thus saving the first 4 partition from search, and query would have been faster

Hope this answers your question.

Answer 3

I solved adding an index only on column id. Queries on partitions are faster even if there isn't the searched id.