MySQL Forums
Forum List  »  InnoDB

Very slow "creating sort index"
Posted by: Stephan Weinberger
Date: July 11, 2018 08:42AM

Good day everyone,

on one of my InnoDB tables some queries are terribly slow; specifically the process hangs in 'creating sort index' for tens of minutes.

The table stores values, indexed by time and various other identifiers, MySQL version is 5.7.22-0ubuntu0.16.04.1.

CREATE TABLE `data` (
`item` mediumint unsigned NOT NULL,
`unixtime` int unsigned NOT NULL,
`datasource` tinyint unisgned NOT NULL,
`parameter` tinyint unsigned NOT NULL,
`value` mediumint NOT NULL,
PRIMARY KEY (`item`,`unixtime`,`parameter`,`datasource`)
) ENGINE=InnoDB

The table is partitioned by range over the `unixtime` column and currently contains some 4 billion values.

The problematic queries have the form:

SELECT `unixtime`, `value` FROM `data` WHERE `item` = 1234 AND `datasource` = 12 AND `unixtime` BETWEEN 12345678 AND 12346789 AND `unixtime`%600 = 0 AND `parameter` = 23 ORDER BY `value` DESC, `time` ASC;

explain shows:
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: data
partitions: p2016_01
type: ALL
possible_keys: PRIMARY
key: NULL
key_len: NULL
ref: NULL
rows: 1
filtered: 100.00
Extra: Using where; Using filesort

If there is NO data for the specified timespan, such queries often stay in the 'creating sort index' state for minutes. This does *not* happen if there are data available!

Especially I don't understand, why
1. the primary index is not used for `item` and a range over `unixtime`
2. mysql does even begin 'createing sort index' when the query returns no rows
3. the 'creating sort index' takes forever

Any clues?



possibly related:

I have a similar table on another machine where the index is different:
Primary key (unixtime,item,parameter,datasource) plus a separate index on (item,unixtime). There the (item,unixtime) index is used as expected:
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: data
type: range
possible_keys: PRIMARY,k_item_time
key: k_item_time
key_len: 7
ref: NULL
rows: 18
Extra: Using where; Using filesort

and the query runs just fine in a few milliseconds. MySQL version there is 5.5.41-0ubuntu0.12.04.1


kind regards,
Stephan

Options: ReplyQuote


Subject
Views
Written By
Posted
Very slow "creating sort index"
33
July 11, 2018 08:42AM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.