How Ordering Slows the Saving of Articles on Large Joomla Sites

Note: The solution presented in this post is a core change. Proceed carefully and always backup your website before modifying core files.

A few days ago, we received an email from someone managing a very large Joomla website (it was a Brazilian news website). She said that the saving of articles was taking an abnormal amount of time. When she first told us that, we were relieved; finally, something easy to work on! After all, it could be either an inflated assets table, or the an abundance of Joomla tags.

We started working on the task immediately, and, unfortunately, we quickly discovered that our excitement for easy work was a bit premature: it was neither the assets table nor the tags. In fact, the client regularly cleaned the assets table and the tags table was maintained at a manageable level. We were still hopeful though, so we started disabling plugins one by one – hoping that this whole issue might be caused by one of the plugins, but that wasn’t the case: we disabled all the plugins, but still we had the same problem. We were finally left with one option: we had to get our hands dirty to get to the bottom of this issue…

So, we enabled MySQL’s slow query logging and added an article to the Joomla website, and we were confident, no, make that positive, that we’ll find a very complex query in the slow query log (after all, the article saving process literally took 5 minutes), but there was nothing there, even though the long-query-time was set to 1 second (e.g. all queries taking more than 1 second were supposed to be logged there).

We were becoming more and more depressed: a task that we thought would bring joy to our hearts was becoming more and more frustrating. But then, something unexpected happened. While trying to save another article, we looked at the browser status bar (which is located at the bottom of the screen), and we noticed that the “Sending Request” part was taking a long time, and then the “Waiting” part was being executed instantly. So, this whole thing all of a sudden seemed like a DNS problem. We asked the client if she had any idea what might be causing this, and she told us that she was using Cloudflare, so we asked her to bypass Cloudflare to see if that solves the problem. She did, and then we tried to add an article, and this time, the whole thing was executed instantly. Hooray, we found the culprit, it was Cloudflare, or was it? You see, despite us feeling happy, we had this strange feeling that we didn’t nail it. After all, why would Cloudflare have this problem only with adding articles? Could it be a firewall rule being triggered on Cloudflare‘s end when someone adds an article? And, if that’s the case, then why weren’t other websites affected? Why just this one? Lieutenant Columbo would have said: “Something’s bothering me…”

So, we did another test to be sure, and this time, we had the problem again: it took about 4 minutes for an article to be saved. Our hunch was right, we didn’t find the solution to the problem… In a desperate move, we decided to debug the article saving process, so we opened the article.php file located under the administrator/components/com_content/models/ folder, and we added a die before the following line…

if (parent::save($data))

…and the die was executed instantly. We then moved the die to after the above line, and it took ages (like minutes), to take effect. So, the problem was really in the save line. Obviously, the next thing to do was to track which queries were executed at the database level by that save line. In order to do that, we debugged the problem at the database level: we printed all the queries before the save method, and then we printed all the queries after the save method (but before everything else), and it turned out that the cause was the following query:

UPDATE `#__content` SET ordering = 106 WHERE `id` = '48129'

In fact, the above query was sent to the database a few thousand times, but with a different ordering and id values. We thought that the client was lucky that saving articles was only taking 4 minutes for the whole thing (each iteration was taking about 50 milliseconds).

It didn’t take us long to find where that query was executed: it was in the table.php file located under the libraries/joomla/table folder. Specifically, the whole thing was taking place in the reorder function of that file. The obvious question at this point would be, what does this function do?

Well, the reorder function is responsible for (you guessed it) ordering articles in Joomla. For new articles, it ensures that the ordering field of the new article is set to 1, and that the ordering of all the other articles in the same category is shifted by 1. Naturally, when you have thousands of articles in each category, then the shifting process will take time.

If you’re thinking that the reorder function is poorly optimized, then you’re right: it is poorly optimized, and the Joomla core development team knows about it. In fact, Joomla versions 3.6.0, 3.6.1, and 3.6.2 addressed this problem, but, by addressing the problem they created an even bigger problem with current websites because the ordering of the new articles in the backend looked messed up (new articles appeared last), and so many Joomla developers voiced deep criticism about this abrupt change, including us. Eventually, the development team had no choice but to revert back the whole thing to the way it originally worked.

In retrospect, we believe that the ordering change in Joomla versions 3.6.0, 3.6.1, and 3.6.2 was done in good faith, but it was incomplete. While these versions fixed the performance problem by no longer shifting the ordering of existing articles by 1 when a new article was inserted, and rather just setting the ordering field of the newly inserted article to the highest ordering + 1, they didn’t address the backend views and the existing data. These versions should have modified the existing views and the existing ordering data to take the new ordering strategy into consideration. Obviously, the second part of the previous sentence (modifying the data) is very dangerous and complex, hence the decision to revert back rather than really addressing the problem.

So, how did we solve the problem?

Luckily, the client wasn’t using the Article Order ordering on any of her Category Blog menu items; she was using the Most Recent First ordering across the board. So, we were able to skip the whole ordering process by adding the following line to the very beginning of the reorder function:

return true;

The saving process worked very smoothly once we added the above line. In fact, it took our client less than a second to save articles on her website after implementing the above patch.

But, what about the ordering of featured articles on the homepage?

The client wasn’t using a menu item of type Featured Articles on the homepage, however, we understand that most Joomla websites do. So, if you want to implement the above solution on your Joomla website and your homepage consists of a Featured Articles menu item, then you will run into issues (the ordering of new articles will be messed up, and the reordering of old articles will no longer work properly). In this case, you will need to add the following lines (instead of the above line) to the beginning of the reorder function:

if ($this->_tbl != '#__content_frontpage')
	return true;

The above will ensure that the #__content_frontpage table will still be reordered by the reorder function. Keep in mind, however, that if your #__content_frontpage table is very large (e.g. has thousands of rows), then adding new articles to your homepage will take a lot of time. It’s always a good idea to ensure that this table contains a thousand entries or less (note that if you have pagination on your homepage, then this pagination will only show a thousand articles if you trim that table to a thousand rows – be very careful). We execute the following query in the cron of the Joomla sites that we fully manage to trim the data in the #__content_frontpage table:

DELETE FROM #__content_frontpage WHERE `ordering` > 1000

But why was the problem briefly solved when the client turned off Cloudflare?

The problem wasn’t solved. It was just a coincidence. When the client turned off Cloudflare, we added an article to a category that had very few articles, and so the reordering process was super quick.

We hope you didn’t find this very long post intimidating. After all, the solution really consists of adding one line (or a couple of lines) to just one file. The rest is purely detective work (or a wild-goose chase, depending on how you see things) and a discussion of the cause of the problem. Having said that, you should do the necessary due diligence before implementing the solution, mainly by ensuring that you are not using the Article Order ordering anywhere on your website. If you do, or if you are not sure, or if you just want help with the implementation, then please contact us. We will always find a solution to your ordering problem, we will ensure that your website remains stable after the implementation, and you won’t have to relinquish your hereditary rights on that beautiful south pacific island in favor of your evil half-sister to pay us our fees!