MySQL: Dramatically slower query execution if use LIMIT 1 instead of LIMIT 5

Question

i noticed a dramatically decrease of speed if i LIMIT a query to 1 instead of 5.

SELECT he. *
FROM homematic_events he
WHERE he.homematic_devices_id =30
ORDER BY id DESC
LIMIT 1 

instead of

SELECT he. *
FROM homematic_events he
WHERE he.homematic_devices_id =30
ORDER BY id DESC
LIMIT 5

My Table contains about 12,000,000 rows with the following structure:

CREATE TABLE IF NOT EXISTS `homematic_events` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `homematic_devices_id` int(11) DEFAULT NULL,
  `address` char(16) COLLATE utf8_unicode_ci NOT NULL,
  `interface_id` char(16) COLLATE utf8_unicode_ci NOT NULL,
  `key` char(32) COLLATE utf8_unicode_ci NOT NULL,
  `value` float(12,2) NOT NULL,
  `timestamp` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `timestamp` (`timestamp`),
  KEY `address` (`address`),
  KEY `key` (`key`),
  KEY `homematic_devices_id` (`homematic_devices_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=12637557 ;

These are explaination an speed measurment for LIMIT 5:

  mysql> EXPLAIN SELECT he. * FROM homematic_events he WHERE he.homematic_devices_id =30 ORDER BY id DESC LIMIT 5;
  +----+-------------+-------+------+----------------------+----------------------+---------+-------+------+-----------------------------+
  | id | select_type | table | type | possible_keys        | key                  | key_len | ref   | rows | Extra                       |
  +----+-------------+-------+------+----------------------+----------------------+---------+-------+------+-----------------------------+
  |  1 | SIMPLE      | he    | ref  | homematic_devices_id | homematic_devices_id | 5       | const | 4171 | Using where; Using filesort |
  +----+-------------+-------+------+----------------------+----------------------+---------+-------+------+-----------------------------+


starting                          0.000010
checking query cache for query    0.000030
Opening tables                    0.000007
System lock                       0.000004
Table lock                        0.000015
init                              0.000019
optimizing                        0.000007
statistics                        0.000098
preparing                         0.000012
executing                         0.000002
Sorting result                    0.022965
Sending data                      0.000047
end                               0.000004
query end                         0.000002
freeing items                     0.000302
storing result in query cache     0.000009
logging slow query                0.000002
cleaning up                       0.000003

These are explaination an speed measurment for LIMIT 1:

mysql> EXPLAIN SELECT he. * FROM homematic_events he WHERE he.homematic_devices_id =30 ORDER BY id DESC LIMIT 1;
+----+-------------+-------+-------+----------------------+---------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys        | key     | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+----------------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | he    | index | homematic_devices_id | PRIMARY | 4       | NULL | 3029 | Using where |
+----+-------------+-------+-------+----------------------+---------+---------+------+------+-------------+

starting                              0.000010
checking query cache for query        0.000034
Opening tables                        0.000009
System lock                           0.000004
Table lock                            0.000015
init                                  0.000020
optimizing                            0.000008
statistics                            0.000069
preparing                             0.000016
executing                             0.000002
Sorting result                        0.000005
Sending data                        502.290180
end                                   0.000010
query end                             0.000003
freeing items                         0.000293
logging slow query                    0.000004
logging slow query                    0.000002
cleaning up                           0.000003

Can anyone explain this behavior to me please? I mention it is a result of the different index that is udes with LIMIT 1. But why does mysql use different keys for different LIMIT values?

Answer 1

With LIMIT 1, I'm guessing the query analyzer zips down the primary key and finds the last record whose homematic_devices_id =30 - presumably because the analyzer knows the "sort" operation will be more expensive.

When you LIMIT 5, I'm guessing the query analyzer decides to find the records first, and then sort them. If you want to speed that operation up, you can create an index on both homematic_devices_id and ID like so: ALTER TABLE homematic_events_test ADD INDEX ( homematic_devices_id, id ) - by putting the device ID first, you accomodate the "Where" clause, and the ID column helps the SORT

Answer 2

For some reason, it's somehow faster for MySQL to use the primary key, ID, to access these rows, instead of the index. Even though the queries you have specifically use the field for which homematic_devices_id index was built. I also find it strange that MySQL in the second case only has homematic_devices_id under possible_keys, but then chooses PRIMARY instead. Usually, MySQL will show both PRIMARY and other possible indexes in that column.

Is it possible that it's a data-dependent problem? Have you tried your query with other device_ids?

Try to use FORCE INDEX in both cases, and see if you can fix the problem.