MySQL 5.7 – Slow Search in Partitioned Table

MySQLmysql-5.7partitioningperformancequery-performance

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?

Best Answer

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 !!!

Related Question