Why are my similar queries processed very differently?

1
votes

We have a table

mysql> show create table channeldata\G
*************************** 1. row ***************************
Table: channeldata
Create Table: CREATE TABLE channeldata (
channel_id smallint(3) unsigned NOT NULL,
station_id smallint(5) unsigned NOT NULL,
time datetime NOT NULL,
reading double NOT NULL DEFAULT '0',
average double NOT NULL DEFAULT '0',
location_lat double NOT NULL DEFAULT '0',
location_lon double NOT NULL DEFAULT '0',
location_alt double(8,3) DEFAULT '0.000',
quality smallint(3) unsigned DEFAULT '0',
PRIMARY KEY (channel_id,station_id,time),
KEY composite3 (station_id,channel_id,quality) USING BTREE,
KEY composite (channel_id,station_id,time,quality) USING BTREE ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
1 row in set (0.00 sec)

Recently I noticed some of the select queries we do are taking quite a long time to finish. The strange thing is that depending on the values of a column in the where clause a select completes either very fast or it takes a long time blocking updates to that same table. I ran these queries through explain:

mysql> explain SELECT reading FROM channeldata WHERE station_id = 6001
AND channel_id = 1 AND time < '2018-09-20T14:58:00'\G
*************************** 1. row ***************************
id: 1 select_type: SIMPLE
table: channeldata
partitions: NULL
type: ref
possible_keys: PRIMARY,composite3,composite
key: PRIMARY
key_len: 4
ref: const,const
rows: 176539
filtered: 33.33
Extra: Using index condition 1 row in set, 1 warning (0.00 sec)

mysql> explain SELECT reading FROM channeldata WHERE station_id = 6001 AND channel_id = 4 AND time < '2018-09-20T14:58:00'\G
*************************** 1. row ***************************
id: 1 select_type: SIMPLE
table: channeldata
partitions: NULL
type: range
possible_keys: PRIMARY,composite3,composite
key: PRIMARY
key_len: 9
ref: NULL
rows: 428073
filtered: 100.00
Extra: Using index condition 1 row in set, 1 warning (0.00 sec)

Why would filtering for a different value (channel_id is 4 instead of 1) make a difference? Both result sets are equal in size. Why is it that on both occasions MySQL chooses to use the PRIMARY key, but that the keylen differs significantly.

1
mysqlindexingquery-performance
You use many fields in keys and they intersect thus giving a lot of work for MySQL inner optimizer (that's why explain shows all 3 possible keys used in select). Try to minimize usage of fields in keys and combine them only when it's necessary and seems logical – Anton
Another problem of using a lot of keys (and badly constructed) is that indexing process starts overwhelm caching possibilities of MySQL itself. In such case you need to perform OPTIMIZE TABLE command or refer to MySQL settings – Anton
why would the PRIMARY key have a key_len of both 4 and 9 ? That looks strange. Also 4 looks really small for a 3 column key. Smallint takes 2 bytes and datetime takes 5 bytes so keylen should have been 9 in both cases. No idea why this would happen though. – apokryfos
Unfortunately I didn't come up with the design of the table. Could you elaborate as to why these keys are badly constructed Anton? The composite keys are there to speed up other queries we use on this table and they seem to work very well. – Lieuwe

1 Answers

1
votes

You have a bug. The T in the time constant is not recognized by MySQL; change it to a space.

You should switch to InnoDB.

In MyISAM, this would speed up the query:

INDEX(channel_id, station_id,  -- in either order
      time,
      reading)   -- last

That would be "covering", hence faster than bounding back and forth between the index and the data.

Back to why they are different... I don't know. However, either of these may help with a MyISAM table: ANALYZE TABLE or OPTIMIZE TABLE.