Efficient pagination with MySQL

Question

I'm trying to speed up pagination of data on my site. I'd like to use the following query, but when I do an EXPLAIN it says it needs to scan over 40,000 rows:

SELECT `Item`.`id`, `User`.`id` 
FROM `items` AS `Item` 
LEFT JOIN `users` AS `User` 
    ON (`Item`.`submitter_id` = `User`.`id`) 
WHERE `Item`.`made_popular` < "2010-02-08 22:05:05" 
  AND `Item`.`removed` != 1 
ORDER BY `Item`.`made_popular` DESC 
LIMIT 26

But if I add a lower bound for the "made_popular" field it only needs to scan 99 rows (the number of items between those two dates).

SELECT `Item`.`id`, `User`.`id`
FROM `items` AS `Item` 
LEFT JOIN `users` AS `User` 
    ON (`Item`.`submitter_id` = `User`.`id`) 
WHERE `Item`.`made_popular` < "2010-02-08 22:05:05" 
    AND `Item`.`made_popular` > "2010-02-07 22:05:05" 
    AND `Item`.`removed` != 1 
ORDER BY `Item`.`made_popular` DESC 
LIMIT 26

Both queries use the index I have on the "made_popular" column. In both cases shouldn't it only need to scan 26 rows, assuming there are no "removed" items? I guess the solution could be to go with the second query and add a lower bound that is probably low enough to give me 26 items, but it seems odd that I should have to do a workaround like that.

LIMIT N,M allows you to set the offset (N) and number of results to return (M). Does LIMIT 1,26 improve performance? — Girish Rao
– Girish Rao, Commented Feb 9, 2011 at 3:32

zerkms · Accepted Answer · 2011-02-09 03:31:54Z

2

but when I do an EXPLAIN it says it needs to scan over 40,000 rows:

It is often to misunderstand what rows means. It means approximate amount of rows that could be used to return the result regardless LIMIT clause.

So you shouldn't worry in this case - mysql stops fetching data right after it reaches LIMIT amount.

answered Feb 9, 2011 at 3:31

zerkms

256k73 gold badges447 silver badges551 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

makeee Over a year ago

Ok, so both these queries should perform exactly the same? Is there any way to find out the number of rows it needs to scan, taking the limit clause into consideration?

zerkms Over a year ago

@makeee: yes. And make sure you have composite index removed + made_popular.

zerkms Over a year ago

@makeee: "Is there any way to find out the number of rows it needs to scan, taking the limit clause into consideration" --- what engine type your tables are? If innodb - then there are some low-level tools that can show how many records actually were fetched from disk.

makeee Over a year ago

@zerkms so an index on made_popular,removed (removed being the second one)? What if less than 1% of rows are "removed"? Is it always worth adding this or is there a tradeoff somewhere, like increased index size? Thanks for the help.

zerkms Over a year ago

show status like '%innodb%'; -- this is how it can be done for innodb. Look at Innodb_data_reads value.

|

Collectives™ on Stack Overflow

Efficient pagination with MySQL

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related