We love optimization work, there’s always something new to discover, and there’s always something new to learn. Our love for optimization led us to discover a gem, a little optimization gem that literally halved the load of a server powering an extremely large and high traffic Joomla website. As usual, we are always thrilled to share our discoveries with our readers, so here goes…
While investigating load issues on a huge Joomla website that we manage and that we previously optimized, we noticed that the following query was taking a relatively long time:
SELECT a.id, a.title, a.alias, a.catid, a.created, a.created_by, a.created_by_alias, a.created as publish_up,a.publish_down,a.state AS state FROM #__content AS a WHERE a.state = 1 AND (a.catid = 55 OR a.catid IN ('300', '301', '302', '303')) AND (a.publish_up = '0000-00-00 00:00:00' OR a.publish_up <= '2017-09-07 18:09:39') AND (a.publish_down = '0000-00-00 00:00:00' OR a.publish_down >= '2017-09-07 18:09:39') ORDER BY a.publish_up DESC, a.created LIMIT 20
The above query is a modification and an optimization of the core Joomla query that is used to generate the list of articles for pages/modules. The modification consists of displaying articles from multiple categories (instead of a specific category), while the optimization consists of an oldie but goldie optimization process that we first implemented on Joomla 2.5, and that is still valid until today (the website in question is Joomla 3.7.5). Having said that, the heart of the query is still representative of the Joomla core query, which means that any problem in the above query is also a problem in an unmodified and an unoptimized core Joomla article selection query.
In order to learn more about the issue, we went to phpMyAdmin, and we added an EXPLAIN directive to the beginning of the above query, and then we executed it. The query executed was as follows:
EXPLAIN SELECT a.id, a.title, a.alias, a.catid, a.created, a.created_by, a.created_by_alias, a.created as publish_up,a.publish_down,a.state AS state FROM #__content AS a WHERE a.state = 1 AND (a.catid = 55 OR a.catid IN ('300', '301', '302', '303')) AND (a.publish_up = '0000-00-00 00:00:00' OR a.publish_up <= '2017-09-07 18:09:39') AND (a.publish_down = '0000-00-00 00:00:00' OR a.publish_down >= '2017-09-07 18:09:39') ORDER BY a.publish_up DESC, a.created LIMIT 20
Here’s what we saw:
If you look at the key in the above image, you will notice that the MySQL engine chose idx_state (which is the state index) for the key, which is far from ideal. The key should be a combination of all the condition fields and all the order fields, which means that in our query above, the key should consist of the following fields: state, catid, publish_up, publish_down, and created. So, we created an index called idx_articles which consists of all those fields using the following query:
ALTER TABLE `#__content` ADD INDEX `idx_articles` ( `state`, `catid`, `created`, `publish_up`, `publish_down`);
After creating the above index, we re-issued the EXPLAIN query and here’s what we saw:
As you can see in the above image, the MySQL database is now choosing the right index that is fully optimized for the article selection query. Additionally, the number of rows examined has decreased considerably.
So, how did this simple optimization affect the server load?
Marvelously, we can safely say! The load was more than halved. It was at 2.2, and it became 0.8, yes, it was that good! If you think it’s too good to be true, then try it and see for yourself (and let us know)! While we don’t guarantee that you will see the same extraordinary result that we saw (especially if you are using extensive caching on your Joomla website), we have the feeling that you will thank us for this.
Now, if you need help with creating the index or if you didn’t see any noticeable gain after creating the index, then please contact us. We are experts in optimizing Joomla websites, and our fees are super reasonable!