The Joomla Authors Filter Field Slows Down Large Sites

Note: This post contains a core modification. Keep in mind that core modifications may cause stability issues and may be wiped out with a simple Joomla update.

A few months back, we’ve written about the Joomla getAuthors function, and how we initially thought it was there to populate the contents of the Author filter field, and how we eventually found out that the function had no purpose whatsoever and wasn’t even responsible for loading the authors.

Needless to say, we were a bit curious back then to know whether loading the authors (that are used for filtering the articles in the backend) used a similar query, but we just didn’t have the time to investigate further…

Fast forward to today, when we found the below slow query while checking the MySQL slow query log on the server of a high traffic Joomla site.

# Query_time: 1.455532  Lock_time: 0.000354 Rows_sent: 33  Rows_examined: 62925
SET timestamp=1521556512;
SELECT u.id AS value, u.name AS text
FROM #__users AS u
INNER JOIN #__content AS c ON c.created_by = u.id
GROUP BY u.id, u.name
ORDER BY u.name;

We immediately thought that the problem was caused by the getAuthors function (which is located in the articles.php file which, in turn, is located under the administrator/components/com_content/models folder), so, we disabled the function by adding a return array(); to its very beginning. Unfortunately, The problem wasn’t fixed as the query still appeared in the slow query log every few minutes.

So, we searched for the files containing the following string “u.id AS value, u.name AS text” in the filesystem of the Joomla site (e.g. under the /home/[cpanel-user]/public_html folder) using the following command:

grep -R 'u.id AS value, u.name AS text' *

And we found that the above query existed in the following 3 files:

  1. administrator/components/com_content/models/articles.php
  2. libraries/cms/html/list.php
  3. libraries/src/Form/Field/AuthorField.php

The first file was clearly not the culprit since we just fixed it. The second one (the list.php file) didn’t have the exact query. So, we were left with the third one, where the query was an exact match.

So, we opened the file AuthorField.php which is located under the libraries/src/Form/Field folder. We scrolled down to the getOptions function, and we changed the following code:

$query = $db->getQuery(true)
	->select('u.id AS value, u.name AS text')
	->from('#__users AS u')
	->join('INNER', '#__content AS c ON c.created_by = u.id')
	->group('u.id, u.name')
	->order('u.name');

to the one below:

$query = $db->getQuery(true)
	->select('u.id AS value, u.name AS text')
	->from('#__users AS u')
	->order('u.name');

We saved the file and then we monitored the site for a few hours and we didn’t see an additional occurrence of the query in the slow query log. Hooray!

So, what is the “AuthorField.php” file?

The AuthorField.php file is the file responsible for displaying the author filter field on the Articles backend page. So you can safely say that the question that we asked ourselves a few months ago (whether the author filter field would cause a performance issue on the site or not) kinda forced us to find its answer.

But, why is the original query slow?

The original query is slow because it only returns the users that have written articles (e.g. the authors), so it does a join operation on the #__content table in order to do that, which is a very expensive operation especially if the #__content table has many articles. Ironically, the purpose of this query is to reduce the load on the browser and also reduce the memory needed to store the users, because some Joomla sites (especially social sites) have thousands of users, and, without this join, all of these users will be returned in the result set and also all of them will be pushed to the browser. That’s why it is not a good idea to implement the above solution on subscription based Joomla sites (please contact us for a customized solution).

We hope that you found our post useful and that implementing the above did speed up your large Joomla site. If you need help with the implementation, then please contact us. We are always eager to help, our fees are affordable, and our Joomla expertise is abundant.

No comments yet.

Leave a comment