One of our newer clients called us yesterday morning and told us that his Joomla website was running very slow. So we checked his website and we immediately knew the cause of the problem, Mosets! If you are a regular reader of our blog, then you probably know that we don’t think highly of this extension – not only because it’s unoptimized by default, but because its code is super messy. But we’re not here to whine, we’re here to fix problems!
So we indexed the should-be-indexed-but-not fields in Mosets’ tables, and that changed things dramatically (in a positive way, of course). However, the website was still running slow, not as slow as before, but still, it was noticeably slow. So, again, we checked the slow query log, and we noticed that there are still many slow queries related to Mosets. Hmmm…
A closer examination of these queries revealed the cause of the slowness, which is the LIMIT command in MySQL. You see, that Mosets instance had over 300k rows in its main table, and when the LIMIT command was used on that table for higher ranges, the slowness occurred. The cause of this problem is the way MySQL handles the LIMIT command (it scans the table until it reaches the starting position of the LIMIT, which means that the higher that starting position is, the more time the query will take to execute).
So, how did we solve the problem?
Usually, when we have a performance bottleneck caused by the MySQL LIMIT command, we add a filter to the query that will start searching only the rows with IDs higher than the starting limit in the LIMIT command. In other words, we modify this query:
SELECT * FROM #__table LIMIT 50000, 30
to this one:
SELECT * FROM #__table WHERE id >= 50000 LIMIT 0, 30
The second query is much, much faster than the first one – but there is one major caveat: The IDs have to be contiguous (e.g. there shouldn’t be any gaps between the IDs of consecutive rows) and the first ID must be 1, because if this is not the case, then the optimized query will yield wrong results.
In order to solve this problem for our client we created a PHP script that made the IDs of the offending table contiguous, with the first ID starting at 1. Of course, the job wasn’t very simple there were other tables using these IDs, and because the content of the Joomla website was also using the old IDs. What made things even more complex was that the links too had to be redirected to the new links (since the old links contained the IDs). Needless to say, it was a very painful experience but the rewards were enormous: the website now runs at lightning fast speed (we did some other optimizations as well) and the client was exalted!
Now, Mosets was just an example, but any table in Joomla (whether a core table or a non-core one) can cause the same issues when using LIMIT commands on higher ranges. So, if you need help making the IDs of a specific table contiguous, then look no further! We have done it before and we know we can do it for you! Just contact us and rest assured that we won’t charge you much and that you will be a very, very happy client!