Why the difference in query time for these two queries?

Question

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

Answer 1

Please provide SHOW CREATE TABLE !

Do you have INDEX(Monitor_Id, Time)? It is the optimal index for the first query. Meanwhile, there is no 'perfect' index for the second query. I would guess that INDEX(Time, Monitor_id) would be best.

What will you do with a resultset of 50K rows? That will choke any web site. If you are processing the data, consider doing some or all of the processing in SQL. It is likely to be much faster than shoveling 50K rows to the client.

Is some combination of columns (other than id) unique? If so, I can probably speed them up even more.

Indexing Cookbook