CODEWITHSUNDEEP
mysqlindexing
Tatu Ulmanen
Tatu Ulmanen

Reputation: 124768

Strange MySQL SELECT execution times

I'm having hard time figuring out why execution times of similar queries differ so much from each other. I have a simple SELECT query like this:

SELECT
    `location_id`,
    `datetime`,
    `year`,
    `month`,
    `load`
FROM  load_report_rows
WHERE location_id = '16583'
AND   load_report_id = '1'
AND   year = '2010'

This query runs merrily in 0.1837 seconds, but if I change the location_id to "18260", the query suddenly takes 2.7012 seconds. All three fields that are used in the WHERE clause are indexed and both queries return exactly 8760 rows. The EXPLAIN for this query returns the following info:

id               1
select_type      SIMPLE
table            load_report_rows
type             index_merge
possible_keys    load_report_id,location_id,year
key              location_id,load_report_id,year
key_len          4,4,4
ref              NULL
rows             3349
extra            using intersect(location_id,load_report_id,year); using where

MySQL query profiler gives the following for each query:

+--------------------------------+----------+----------+
| Status                         | Query 1  | Query 2  |
+--------------------------------+----------+----------+
| starting                       | 0.000023 | 0.000023 |
| checking query cache for query | 0.000072 | 0.000068 |
| checking permissions           | 0.000010 | 0.000068 |
| opening tables                 | 0.000012 | 0.000012 |
| system lock                    | 0.000005 | 0.000004 |
| table lock                     | 0.000008 | 0.000008 |
| init                           | 0.000050 | 0.000026 |
| optimizing                     | 0.000030 | 0.000014 |
| statistics                     | 0.000461 | 0.001048 |
| preparing                      | 0.000022 | 0.000043 |
| executing                      | 0.000003 | 0.000004 |
| sending data                   | 0.100939 | 2.649942 |
| end                            | 0.000013 | 0.000040 |
| end                            | 0.000004 | 0.000004 |
| query end                      | 0.000004 | 0.000004 |
| freeing items                  | 0.000012 | 0.000013 |
| closing tables                 | 0.000008 | 0.000008 |
| logging slow query             | 0.000002 | 0.000003 |
| cleaning up                    | 0.000006 | 0.000005 |
+--------------------------------+----------+----------+

The sending data stage takes significantly longer with the second query. What steps are included in that? Here's the table structure for the relevant fields if that helps:

`id`              int(11)
`load_report_id`  int(11)
`location_id`     int(11)
`datetime`        datetime
`year`            int(4)
`month`           int(2)
`load`            decimal(16,8)

PRIMARY KEY  (`id`)
KEY `load_report_id` (`load_report_id`)
KEY `location_id` (`location_id`)
KEY `year` (`year`)
KEY `month` (`month`)

Any ideas what could cause the second query run so slowly?

Upvotes: 1

Views: 238

Answers (4)

Brent Baisley
Brent Baisley

Reputation: 12721

My guess would be caching. You are causing MySQL to caste/convert your values (strings/numbers). All the fields you are searching on are int, but you are passing mysql a string to search on. Remove the quotes from around the numbers you are searching on and see what happens.

MySQL may be converting all the values in the table to strings in order to compare, rather than converting the search string to a number. You only posted 1 explain query, you should post both to see if the execution paths are different.

Upvotes: 1

kijin
kijin

Reputation: 8910

sending data is a misleading description. It actually includes both the time spent executing the query and the time spent sending the result over the wire. source 1 source 2 source 3

Since both queries generate approximately the same amount of data, the difference must be in the execution stage. There are a few possibilities:

  • Maybe the three indexes don't generate a useful intersection in the right order when location_id is 18260. Try adding a multi-column index as @Ike Walker suggested.

  • Maybe the rows to be returned in the second query are fragmented all over the disk. This would make MySQL spend a considerable amount of time just waiting for the disk to seek to the next location. Try optimizing your table. Fragmentation can also happen with heavily used indexes, so also try dropping and recreating some indexes.

Upvotes: 2

Ike Walker
Ike Walker

Reputation: 65547

I have a couple of suggestions.

First, I would add a multi-column index to cover those 3 columns. This way you can scan a single index rather than doing an index merge:

ALTER TABLE load_report_rows
  ADD KEY location_report_year_idx (location_id,load_report_id,year);

The new index makes the location_id index somewhat redundant, so you may want to consider dropping it as well.

Secondly, I would recommend rewriting the query to input the ints as ints, not strings. This will avoid an unnecessary implicit type conversion which may lead to slower performance. It's probably not the cause of your specific problem, but I think it's a good practice in general:

SELECT
    `location_id`,
    `datetime`,
    `year`,
    `month`,
    `load`
FROM  load_report_rows
WHERE location_id = 16583
AND   load_report_id = 1
AND   year = 2010

Upvotes: 1

Chris Henry
Chris Henry

Reputation: 12010

The sending data state refers to when MySQL is actually sending data over the wire. So the slowness could very well be because id 18260 simply returns many more rows than your first query.

Upvotes: 0

Related Questions