Take the 2-minute tour ×
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. It's 100% free, no registration required.

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?

share|improve this question
    
I'm sorry, but I don't understand the point of the subquery in your second statement. Can you explain? –  Derek Downey Oct 29 '12 at 14:22
add comment

2 Answers

up vote 3 down vote accepted
SELECT id FROM pixels WHERE pixel_id='some_pixel_id' ORDER BY id LIMIT n, 1 
share|improve this answer
    
precisely what I was thinking so +1 :) –  Derek Downey Oct 29 '12 at 14:39
    
@david: +1 (althought it's not entirely equivalent). I guess you mean ORDER BY id DESC LIMIT n, 1 –  ypercube Oct 29 '12 at 14:39
    
The "not entirely equivalent" goes to the fact that this will not return the same as the original, if there are fewer than 1000 ids. –  ypercube Oct 29 '12 at 14:41
    
What are the performance considerations? –  Adam Matan Oct 29 '12 at 14:53
add comment

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.

share|improve this answer
    
+1 Thanks! Null is actually desired when there are less than n results. –  Adam Matan Oct 29 '12 at 16:18
add comment

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.