StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POMySQL: Dramatically slower query execution if use LIMIT 1 instead of LIMIT 5
text
Body
copied!<p>i noticed a dramatically decrease of speed if i LIMIT a query to 1 instead of 5. </p> <pre><code>SELECT he. * FROM homematic_events he WHERE he.homematic_devices_id =30 ORDER BY id DESC LIMIT 1 </code></pre> <p>instead of</p> <pre><code>SELECT he. * FROM homematic_events he WHERE he.homematic_devices_id =30 ORDER BY id DESC LIMIT 5 </code></pre> <p>My Table contains about 12,000,000 rows with the following structure:</p> <pre><code>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 ; </code></pre> <p>These are explaination an speed measurment for LIMIT 5:</p> <pre><code> 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 </code></pre> <p>These are explaination an speed measurment for LIMIT 1:</p> <pre><code>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 </code></pre> <p>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?</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload