Pdopage's use of SQL_CALC_FOUND_ROWS crushes the server

Development
1

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.

2

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:

3

what is your pdoPage - call?

4

Hi,

Thank you for your reply.
This is my call:

[[!pdoPage?
                &parents=`[[*id]]`
                &tpl=`og.ArticleRowTpl`
                &tpl_1=`og.ArticleRowTpl.z2`
                &tpl_2=`og.ArticleRowTpl.z3`
                &tpl_4=`og.ArticleRowTpl.z4`
                &tpl_5=`og.ArticleRowTpl.z4d`
                &tpl_7=`og.ArticleRowTpl.z6`
                &tpl_8=`og.ArticleRowTpl.z7`
                &tpl_9=`og.ArticleRowTpl.z8`
                &includeTVs=`mainImg`
                &processTVs=`1`
                &select=`{"modResource":"id,pagetitle,content,publishedon"}`
                &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.

5

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

               &includeTVs=`mainImg`
               &processTVs=`1`
6

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

7

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.

8

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.

9

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.

10

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