Joomla, Pagination, and Performance Issues: Again and Again

Warning: The code changes in this post are in the core. Proceed with caution (and at your own risk) and keep in mind that future Joomla updates may wipe out the below changes.

In a previous post, we have explained how we resolved performance issues caused by pagination on a large Joomla website. The results were satisfactory, but not conclusive: we still had the problem in the backend and we were still seeing the following query (along with some variations of it) in the MySQL slow query log:

SELECT COUNT(*)
FROM myj63_content AS a
LEFT JOIN myj63_users AS uc ON uc.id=a.checked_out
LEFT JOIN myj63_viewlevels AS ag ON ag.id = a.access
LEFT JOIN myj63_categories AS c ON c.id = a.catid
LEFT JOIN myj63_users AS ua ON ua.id = a.created_by
WHERE (a.state = 0 OR a.state = 1);

So, although we solved the issue with excessive pagination, Joomla still was calculating the total items for each particular view, and that calculation was slowing down the whole server. We resolved the issue the following way:

  • We opened the file list.php located under the libraries/legacy/model folder.

  • We changed the following line:

    $page = new JPagination($this->getTotal(), $this->getStart(), $limit);

    to this line:

    $page = new JPagination(2000, $this->getStart(), $limit);

  • We uploaded the file back, we cleared the MySQL slow query log, and then we checked if any new slow queries were written.

  • We waited for an hour and no new query was written to the file. The mission was successful!

But, the next day, we checked the slow query log and we noticed that there were new COUNT(*) queries that were recorded. After a quick investigation we realized that we had to modify another file in order to really close the problem, and that file was the legacy.php located under the libraries/legacy/model folder. Here’s what we did:

  • We opened the aforementioned file.

  • At the very beginning of the _getListCount function, we added the following line:

    return 2000;.

  • We saved the file and we uploaded it back.

  • The problem was solved!

Now, you might be wondering, what’s with the 2000? Well, the 2000 is a hardcoded number representing the number of entries in any view. For example, if a category has 10,000 articles, then we are telling Joomla that it has only 2,000 articles, which is OK (because we are not allowing pagination past page 100). But, what if a category only had 10 articles? This means that for that category, we will be showing that it has 100 pages when it only has 1 page. That doesn’t look very nice, but it cannot be handled with a single line of code. So, if you want to implement the solution above, you will need to live with this limitation, or you will need to ask some Joomla experts (such as your humble servants), to address this for you. If you want to do this yourself, then let us give you a hint: you will need to run a midnight cron job calculating the number of items for each view, and, if that number is less than 2000, then you should use that number in the files above instead of 2000.

If you want help implementing the above solution, then please contact us. We are happy to serve, we work very quickly, and our fees are very affordable!

No comments yet.

Leave a comment