Pdopage's use of SQL_CALC_FOUND_ROWS crushes the server

Hello everyone,

I’d really appreciate if anyone can help me with this.
I’m working on a news portal site. The site is rather big, at the moment having over 200k articles. Recently the site started crashing, the problem being a very slow query that seems to be created by pdopage.
Is there some other extra that I can use for pagination that would work for large sites? Or what else could I do to fix it.

Hey :wave:

The only pagination extras I know off are pdoPage and getPage. You could try swapping it out for getPage, but as pdoPage is marketed as being a more performant alternative I’m kinda sceptical that will improve it.

What might be a better avenue is to see if your experience and particularly large site can help make pdoPage better. It seems you’ve identified the specific query - if you can also identify a way to make that query better so it doesn’t crush the server, I’m sure that would be a very welcome contribution to pdoTools. :slight_smile:

what is your pdoPage - call?


Thank you for your reply.
This is my call:

                &sortby 	    = `modResource.publishedon`
                &sortdir 	    = `DESC`

It is used in the template for an Articles container. There are several categories in the site, each being a container.

do you have the same issue with a simplyfied call, for example without


I’m trying it now. It takes some time until the error happens. I’ll see what happens.

If it’s really SQL_CALC_FOUND_ROWS that causes the problem, then maybe switching to getPage/getResources helps (as SQL_CALC_FOUND_ROWS isn’t used in these snippets).

For pdoPage to work without SQL_CALC_FOUND_ROWS, you would have to make some changes to the code of the class pdoFetch. You’d basically have to make a second database query (using COUNT(*) instead) to get the total value.

When does the error occur? When you try to load the last page or also on one the first pages? Using a high value for OFFSET can make a query quite slow.

see MySQL :: WL#12615: Deprecate SQL_CALC_FOUND_ROWS and FOUND_ROWS

seems SQL_CALC_FOUND_ROWS can really be very slow with a lot of items.
I think xpdo->getCount would work better here, which is used by getResources

You should report that issue at the pdoTools - repo, which Mark allready has linked.

Thank you all for your replies.
@halftrainedharry The issue occurs mostly when trying to access some of the last pages, and the number of pages per category can get into the thousands.

I tried using getPage instead, but for some reason nothing was displaying. In the end, I used the Articles default pagination, it’s been ok so far.

Internally, the extra “Articles” uses getPage as well for the pagination.
But I’m glad it works now.