While optimizing an already optimized K2 powered Joomla website yesterday, we noticed the following query in the slow query log:
SELECT i.*, c.name as categoryname,c.id as categoryid, c.alias as categoryalias, c.params as categoryparams FROM #__k2_items as i RIGHT JOIN #__k2_categories AS c ON c.id = i.catid WHERE i.published = 1 AND i.access IN(1,1,5) AND i.trash = 0 AND c.published = 1 AND c.access IN(1,1,5) AND c.trash = 0 AND ( i.publish_up = '0000-00-00 00:00:00' OR i.publish_up <= '2017-10-31 14:53:20' ) AND ( i.publish_down = '0000-00-00 00:00:00' OR i.publish_down >= '2017-10-31 14:53:20' ) AND c.id IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17) AND i.featured = 1 AND i.id > 25000 ORDER BY i.featured DESC, i.created DESC LIMIT 0, 10;
The above query typically runs on the homepage of the high traffic Joomla site, and is used to return a list of featured K2 items belonging to (K2) categories 1 to 17. The query, as you may have already noticed, is already optimized. However, it is slow, and this is because of the following:
c.id IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17)
As you can see in the above, the query uses MySQL’s IN, which is very expensive. Well, not as expensive as buying a whole watermelon in Iqaluit, Nunavut (this is the in North Pole, in case you just don’t have time to google it), but still, really expensive. The reason why the query uses IN is to restrict the search to a number of specific categories. But, is this really necessary?
In the case of this website, it was not. First, all of the K2 categories on the Joomla site consisted of the 17 categories listed above, and second, even if there were other K2 categories than the ones listed above, the filtering on the featured field was enough (the featured field is mainly used to filter the items to be displayed on the homepage). So, we logged in to the backend of the website, and we went to the Home menu item (e.g. the menu item where the Home flag is set), and we clicked on options, and we removed all the categories from Select Categories. We saved the menu item and then we cleared the cache. We then verified that the website was showing the same content that it was displaying before on the homepage (just to make sure that we didn’t break something in the process).
After doing the above, we monitored the slow query log for that website for 24 hours, and, guess what, there were no more slow queries! Hooray!
So, in case you are having some performance issues on your K2 powered website, then make sure you are not filtering on categories when you don’t need to do so (if you are displaying only featured items on the homepage, then it is almost a certainty that you don’t need to have any filtering on the categories). If the K2 performance issues are caused by something else, then please contact us. We are experts in Joomla optimization, our work is super clean, and our fees are super affordable!