CODEWITHSUNDEEP
mysqlindexingquery-performance
Lieuwe
Lieuwe

Reputation: 1840

Why are my similar queries processed very differently?

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.

Upvotes: 1

Views: 42

Answers (1)

Rick James
Rick James

Reputation: 142298

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.

Upvotes: 1

Related Questions