Edit Question - KnowledgeHub

Title Body

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

Tags (comma-separated) Cancel