I have a MySQL 5.7 database with a large table that has about 70,000,000 rows. There are 4 columns: Id
, Time
, Value
, Monitor_Id
. Id
is the primary key, Time
is indexed and Monitor_Id
is a foreign key. Value
is a varchar(256)
.
I am doing two different queries:
SELECT * FROM monitorsamples where Time > '2019-06-28' and Monitor_Id = 19 order by Time desc;
SELECT * FROM monitorsamples where Time > '2019-06-28' and Monitor_Id >= 19 and Monitor_Id <= 35 order by Time desc;
The first query returns about 10,500 rows. The second query returns the cap of 50,000 rows. The first query takes about 10 seconds to complete and the second one takes less than 1/10 of a second. I am trying to understand why the more restrictive query takes longer?
This makes no sense to me. Anyone have an explanation for this?
UPDATE:
Here is the DDL for creating the table:
CREATE TABLE `monitorsamples` (
`Id` int(11) NOT NULL AUTO_INCREMENT,
`Time` datetime NOT NULL,
`Value` varchar(256) CHARACTER SET utf8 NOT NULL,
`Monitor_Id` int(11) NOT NULL,
PRIMARY KEY (`Id`),
UNIQUE KEY `Id` (`Id`),
KEY `Monitor_Id` (`Monitor_Id`),
KEY `Time` (`Time`),
CONSTRAINT `MonitorSample_Monitor` FOREIGN KEY (`Monitor_Id`) REFERENCES `monitors` (`Id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1
Hope this provides some insight into the problem. I think there must be some fundamental DB magic happening under the hood that optimizes "x >= y and x <= z" versus the simpler "x = y". It is very counterintuitive.
UPDATE 2:
Here are the explains for both queries. The first one is the simpler query (first query from above).
select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, Extra
'SIMPLE', 'monitorsamples', NULL, 'ref', 'Monitor_Id,Time', 'Monitor_Id', '4', 'const', '1040640', '5.60', 'Using index condition; Using where; Using filesort'
'SIMPLE', 'monitorsamples', NULL, 'range', 'Monitor_Id', 'Monitor_Id', '4', NULL, '9702114', '100.00', 'Using index condition; Using filesort'
I am not sure why the second query does not use where
. And it looks like the second query uses few sorting and selection mechanisms and still executes faster.
UPDATE 3:
I am adding the explains in JSON format for each query to provide more detail.
Query 1:
{
"query_block" : {
"cost_info" : {
"query_cost" : "773972.00"
},
"ordering_operation" : {
"table" : {
"access_type" : "ref",
"attached_condition" : "(`monitorsamples`.`Time` > '2019-06-28')",
"cost_info" : {
"data_read_per_join" : "53M",
"eval_cost" : "14293.85",
"prefix_cost" : "773972.00",
"read_cost" : "576000.00"
},
"filtered" : "7.22",
"index_condition" : "(`monitorsamples`.`Monitor_Id` <=> 19)",
"key" : "Monitor_Id",
"key_length" : "4",
"possible_keys" : [
"Monitor_Id",
"Time"
],
"ref" : [
"const"
],
"rows_examined_per_scan" : 989860,
"rows_produced_per_join" : 71469,
"table_name" : "monitorsamples",
"used_columns" : [
"Id",
"Time",
"Value",
"Monitor_Id"
],
"used_key_parts" : [
"Monitor_Id"
]
},
"using_filesort" : true
},
"select_id" : 1
}
}
Query 2:
{
"query_block" : {
"cost_info" : {
"query_cost" : "7243060.61"
},
"ordering_operation" : {
"table" : {
"access_type" : "range",
"attached_condition" : "((`monitorsamples`.`Monitor_Id` >= 19) and (`monitorsamples`.`Monitor_Id` <= 35))",
"cost_info" : {
"data_read_per_join" : "804M",
"eval_cost" : "215254.77",
"prefix_cost" : "7243060.61",
"read_cost" : "7027805.84"
},
"filtered" : "20.80",
"index_condition" : "(`monitorsamples`.`Time` > '2019-06-28')",
"key" : "Time",
"key_length" : "5",
"possible_keys" : [
"Monitor_Id",
"Time"
],
"rows_examined_per_scan" : 5173614,
"rows_produced_per_join" : 1076273,
"table_name" : "monitorsamples",
"used_columns" : [
"Id",
"Time",
"Value",
"Monitor_Id"
],
"used_key_parts" : [
"Time"
]
},
"using_filesort" : false
},
"select_id" : 1
}
}
LIMIT 50000
.(time, monitor_id)
and compare again both queries. Then delete that index, add one at(monitor_id, time)
and compare again both queries. There is no magic.PRIMARY KEY
is aUNIQUE
key, so theUNIQUE(id)
is redundant and should be dropped.