We were commissioned yesterday to optimize a large Joomla website. That website was using FlexiContent as its content engine. In case you don’t know, FlexiContent extends Joomla’s core content management system, and does not replace it (unlike K2). The main reason why our client was using FlexiContent was that it supports multiple categories (per article). We aren’t hugely fond of that extension…
In any case, the website was super slow. So, as usual, we checked the slow query log and we noticed that the following query was taking some insanely long time to execute:
SELECT * from `#__flexicontent_content_hits` WHERE content_id < (SELECT max(content_id) - 200 FROM `#__flexicontent_content_hits`);
As you can see, the above query is technically a query within a query (which is called a subquery). While this works, it is super inefficient since MySQL is not the best RDBMS when it comes to subqueries (other RDBMS‘s fair much better when using subqueries).
But why is MySQL slow for subqueries?
MySQL executes all its queries outside-in (that’s the opposite of inside-out, in case you’re wondering, but you’re probably not). In other words, MySQL runs the outside query first, and then, it will execute the inside query for every single row returned from the outside query, which means that if the #__flexicontent_content_hits table has 4,000,000 rows, then this will technically mean that the above query is the equivalent of 4 million queries. Not good at all! In fact, we think that the client was lucky that his website still worked (albeit very slowly) considering the number of rows he had in that table.
So, what did we do to fix the problem?
In order to fix the problem, we split the query into two queries (the following is a pseudocode):
FIRST_QUERY_RESULTS = SELECT max(content_id) - 200 FROM `#__flexicontent_content_hits`;
SECOND_QUERY_RESULTS = SELECT * from `#__flexicontent_content_hits` WHERE content_id < FIRST_QUERY_RESULTS;
The above made a huge difference on the server’s load, and, ultimately, on the website’s speed. This is because the above method meant that the second query consisted only of one query, and not of n queries, where n is the number of rows in the `#__flexicontent_content_hits` table.
So, we have a message to all Joomla developers out there (and to all LAMP developers): Do not use subqueries, they create horrible performance issues and the alternative is very, very simple – just split your query into two and that’s it.
Now, if your website suffers from performance issues (that may or may not be related to subqueries), then just contact us and you can rest assured that your website will become quicker than ever with us. Keep in mind that both our very affordable fees and our love apply!