Should I use SQL limit or not for performance reason?

Question

I'm using Doctrine 2 for a project, it'll have a high traffic, and I'm concerned about performance.

Sometimes I'll have to load a lot entities for "pagination" purposes.

Example: loading 30,000 published articles and I'll need to paginate these results.

I'm wondering how bad is it to load so much rows from databases where I could use LIMIT OFFSET sql statements, however using Doctrine 2 & Pagination, It'll be much more overhead to implement a Paginator adapter (complex repositories, etc.) where I could use a simple Iterator Adapter.

I guess with a good caching system, it shouldn't be a problem, but I'm not really sure.

By the way, do you have any tips about the cache?

Answer 1

The question is, with whatever pagination solution you go with, does it actually load 30,000 records when you're only displaying 10 on a page, or does it only load the 10 needed?

If it does load all the records just to display the 10, then it's insane, and you'll have performance problems. Any pagination solution that's actually worth using will load only the records necessary.

Also, cache isn't intended to solve these kinds of problems (i.e to hide an inefficient algorithm). Write efficient code, that is fast, and cache will make your responses even faster.

Finally, in a typical application, the space allotted for fast cache is very precious, so don't fill it up with a bunch of stuff you don't need. Keep it tight so your cache can help make as much of your app as speedy as possible.

Answer 2

I've not used Doctrine before, but almost all the approaches I've seen to pagination definitely do not load the full table of data to do pagination. In the most basic way, you do two queries: one to calculate the number of records (SELECT COUNT(*) ...) and another to get the actual rows you need (SELECT * ... LIMIT ...). MySQL provides a nice simplification of this with SQL_CALC_FOUND_ROWS and FOUND_ROWS.

However, I worked on a project once where the data views I needed involved some rather complex joins of several tables with tens of millions of records. Performing the COUNT(*) was taking upwards of 8 seconds per hit. What I ended up doing for pagination was to take a slightly more realistic approach: People don't really use pagination. I mean, you don't go past page one 99% of the time, right? The amount of people who would go past page 10 is minuscule, so I changed my query to select the first pageSize * 10 + 1 records (201, in my case). If the number of records found was 201, then I simply printed "You are on page 1 of 'lots'".