Mysql – slow select query on datetime column

MySQLperformancequery-performance

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?

Best Answer

Here's one attempt:

SELECT MIN(created_at), MAX(created_at) 
FROM (
    SELECT created_at
    FROM runs 
    ORDER BY id DESC 
    LIMIT 10000
) as x;

Your query is evaluated as:

SELECT MIN(created_at), MAX(created_at) FROM runs

Then it is ordered according to id (which doesn't make sense) and a maximum of 10000 rows is returned (only 1 row exists)

My query first picks the 10000 most recent id's

SELECT created_at
FROM runs 
ORDER BY id DESC 
LIMIT 10000

From there it picks min and max for created_at

Related Question