0

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
    }
}
  • You say "cap of 50,000 rows", but I don't see LIMIT 50000. – Rick James Jul 5 at 18:17
  • It is the built-in GUI limit for Workbench. I have it set to 50K. – Psyfun Jul 7 at 2:56
  • Add an index on (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. – ypercubeᵀᴹ Jul 8 at 11:20
  • Note that the faster one is "Using filesort"! (It is faster because it touches a lot fewer rows, thereby more than compensating for the 'filesort'.) – Rick James Jul 12 at 4:56
  • A PRIMARY KEY is a UNIQUE key, so the UNIQUE(id) is redundant and should be dropped. – Rick James Jul 12 at 4:59
2

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

  • I don't want 50K rows. I just wonder why 50K rows performs better than the first query that returns 10K rows when they are essentially doing the same thing. More restrictive should perform better. – Psyfun Jul 7 at 2:57
  • @Psyfun - I need to see SHOW CREATE TABLE to explain the difference. – Rick James Jul 7 at 4:02
  • No worries. I updated the question to provide the information you were looking for. Hope this helps. Also, the goal for the query is to return the previous X days worth of data for generating a graph/chart. – Psyfun Jul 8 at 11:17
  • 1
    @Psyfun - Sorry, but I need something else: EXPLAIN SELECT ... -- for each of the selects. I suspect it is using a different index. – Rick James Jul 8 at 13:55
  • I added the explains. Hope that sheds some light on why these queries are operating and performing differently. – Psyfun Jul 10 at 13:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.