17
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.0