I have a table in MySQL which has approximately 10 million records, and now a query which used to take micro seconds now takes more that 8 seconds to execute! Here is my table structure:
CREATE TABLE `runs` ( `id` INT(11) NOT NULL AUTO_INCREMENT, `runstub` VARCHAR(20) DEFAULT NULL, `userid` INT(11) DEFAULT NULL, `processid` INT(11) DEFAULT NULL, `rundata` VARCHAR(255) DEFAULT NULL, `token` VARCHAR(60) DEFAULT NULL, `created_at` DATETIME DEFAULT NULL, PRIMARY KEY (`id`), KEY `created_at` (`created_at`) ) ENGINE=INNODB AUTO_INCREMENT=10039971 DEFAULT CHARSET=latin1;
and here is the query that takes more than 8 secs...
SELECT MIN(created_at), MAX(created_at) FROM runs ORDER BY id DESC LIMIT 10000
Is there a way I can optimise this query to get it run in less than a second?
This is the result I want to achieve: From the last 10k records (not all), the time it took. I want the time it started (
MIN(created_at)) and the time it ended (
MAX(created_at)). How can I achieve that without the limit?