Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
1  
Your query does not do what the description wants it to do. You must change to something like Lennart's answer. – Rick James Jan 11 at 0:06
up vote 9 down vote accepted

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

share|improve this answer

if the query used to take microseconds and now takes minutes means you reach a limit... probably query buffer.

Check your my.cnf file and increase or set the following params

myisam_sort_buffer_size = 128M
max_connections         = 4096
sort_buffer_size        = 32M
read_buffer_size        = 256K
read_rnd_buffer_size    = 8M
wait_timeout            = 10
connect_timeout         = 5
interactive_timeout     = 10
concurrent_insert       = 2
thread_concurrency      = 0
tmp_table_size          = 64M
max_heap_table_size     = 512M
share|improve this answer
1  
None of these are relevant. Do not tweak any of them. The "Query cache" could explain the difference. – Rick James Jan 11 at 0:05

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.