CODEWITHSUNDEEP
mysqloptimization
Question Overflow
Question Overflow

Reputation: 11255

How to select last N Rows without using an Index

I have a query that contains several conditions to extract data from a table of 5 million rows. A composite index has been built to partially cover some of these conditions to the extend that I am not able to cover the sorting with an index:

SELECT columns FROM Table WHERE conditions='conditions' ORDER BY id DESC LIMIT N;

The id itself is an auto-increment column. The above query can be very slow (4-5s) as filesort is being used. By removing the ORDER BY clause, I am able to speed up the query by up to 4 times. However the data extracted will be mostly old data.

Since post-processing can be carried out to sort the extracted data, I am more interested in extracting data from roughly the latest N rows from the resultset. My question is, is there a way to do something like this:

SELECT columns FROM Table WHERE conditions='conditions' LIMIT -N;

Since I do not really need a sort and I know that there is very high likelihood that the bottom N rows contain newer data.

Upvotes: 0

Views: 1646

Answers (2)

Buttle Butkus
Buttle Butkus

Reputation: 9456

Here you go. Keep in mind that there should be no problem in using ORDER BY with any indexed columns, including id.

SET @seq:=0;
SELECT  `id` 
FROM (

SELECT @seq := @seq +1 AS  `seq` ,  `id` 
FROM  `Table` 
WHERE `condition` =  'whatever'
)t1
WHERE t1.seq
BETWEEN (
(

SELECT COUNT( * ) 
FROM  `Table` 
WHERE `condition` =  'whatever'
) -49
)
AND (

SELECT COUNT( * ) 
FROM  `Table` 
WHERE `condition` =  'whatever'
);

You can replace the "-49" with an expression like: -1 * ($quantity_desired -1);

Also check out this answer as it might help you: https://stackoverflow.com/a/725439/631764

And here's another one: https://stackoverflow.com/a/1441164/631764

Upvotes: 2

Bohemian
Bohemian

Reputation: 424983

Grab the last "few" rows using a between:

SELECT columns 
FROM Table 
WHERE conditions = 'conditions'
AND id between (select max(id) from table) - 50 AND (select max(id) from table)
ORDER BY id
DESC LIMIT N;

This example gets the last 50 rows, but the id index will be used efficiently. The other conditions and ordering will then be only over 50 rows. Should work a treat.

Upvotes: 1

Related Questions