I have mongodb and NodeJs. The connection is done via mongoosejs.
What is the best way to develop ajax infinity scroll ? Should I use limit and offset ?
Asked
Active
Viewed 9,414 times
1 Answers
36
"skip and limit" approach is not very efficient when you are paging far into dataset. It is effectively a Shlemiel the Painter's algorithm.
Range queries are much more efficient (when supported by indexes). For example, let's imagine that you're displaying tweets. Your page size is 20 and you're on page 1000 and want to load page 1001.
This query
db.tweets.find().sort({created_at: -1}).skip(1001*20).limit(20)
is much less efficient than
db.tweets.find({created_at: {$lt: last_displayed_date}}).
sort({created_at: -1}).limit(20);
(provided that you have index on created_at
).
You get the idea: when you load a page, note the timestamp of the last tweet and use it to query the next page.
![](../../users/profiles/125816.webp)
Sergio Tulentsev
- 210,238
- 40
- 347
- 343
-
1What to do if there are some tweets created at the same time by different users but I need to show them ? – Oleg Dats Apr 09 '12 at 11:55
-
Exactly the same millisecond? I don't think so. – Sergio Tulentsev Apr 09 '12 at 11:55
-
1If that's the case, sort by `created_at: -1, user_id: 1` – Sergio Tulentsev Apr 09 '12 at 11:56
-
1It will not work in case of sorting by another date. For example: 1. User can filter events by date (only by day without hours) 2. There are 300 events created on different dates (created_at different) and they will occur (event_date) on different dates. 3. How to filter them and load by paging ? NOTE: it's impossible to sort by the event_date because this date is rounded and not unique – Oleg Dats Apr 09 '12 at 13:51
-
5Man, come up with something. I gave you the basic idea :) – Sergio Tulentsev Apr 09 '12 at 13:54
-
1I see ) But the 'basic idea' works only with basic problem similar to display items ordered by unique field (like date of creation). It will not work if you need to display ordered (by not unique filed) list of items. – Oleg Dats Apr 09 '12 at 14:17
-
2Order by `event_date, event_id`. – Sergio Tulentsev Apr 09 '12 at 14:20
-
sorry for disturbing, my mistake. Thanks one more time – Oleg Dats Apr 09 '12 at 14:28
-
How do I filter results? ie match substring 'foo' in name for example – chovy Mar 05 '17 at 19:52
-
@chovy: exactly the same way you'd do if you weren't paginating – Sergio Tulentsev Feb 15 '18 at 10:09