MySQL: SELECT the n-th item from an ordered result

Question

SELECTing the top n elements from a table is easy:

SELECT id FROM pixels WHERE pixel_id='some_pixel_id' ORDER BY id DESC LIMIT 1000;

Finding the n-th element with a subquery is quite straightforward:

SELECT MIN(id) FROM 
    (SELECT id FROM pixels WHERE pixel_id='some_pixel_id' ORDER BY id DESC LIMIT 1000) s;

Assuming that there is a BTREE index on the relevant columns (id), is this approach efficient? Are there any better alternatives?

Answer 1

SELECT id FROM pixels WHERE pixel_id='some_pixel_id' ORDER BY id LIMIT n, 1 

Answer 2

This is only an improvement over @david's query, so you have the same result when there are less than 1000 rows in the intermediate set:

( SELECT id 
  FROM pixels 
  WHERE pixel_id='some_pixel_id' 
  ORDER BY id DESC
    LIMIT 1 OFFSET 999 
)
UNION ALL
( SELECT MIN(id)
  FROM pixels 
  WHERE pixel_id='some_pixel_id' 
)
ORDER BY id DESC
  LIMIT 1 ;

The best index for any of these queries is on (pixel_id, id), not on (id) alone.