Optimizing JComments in Joomla’s Backend

A client of us called us and told us that they were were experiencing huge slowdowns when using JComments in the backend – such slowdowns were affecting the whole website and sometimes lasting for several minutes! After a quick investigation in the slow query log, we were able to isolate the 3 queries causing the problem:

• Query 1: (sometimes taking about 60 seconds to execute):

  SELECT jc.*, (SELECT COUNT(*) FROM `#__jcomments_reports` AS r WHERE r.commentid = jc.id) AS reports,jo.title as object_title, jo.link as object_link,u.name AS editor
FROM `#__jcomments` AS jc
LEFT JOIN `#__jcomments_objects` AS jo ON jo.object_id = jc.object_id AND jo.object_group = jc.object_group AND jo.lang = jc.lang
LEFT JOIN `#__users` AS u ON u.id = jc.checked_out
ORDER BY jc.date desc LIMIT 0, 20;

• Query 2: (sometimes taking about 5 seconds to execute):

  SELECT DISTINCT(lang) AS name
FROM #__jcomments
ORDER BY object_group ASC;

• Query 3: (sometimes taking about 5 seconds to execute):

  SELECT DISTINCT(object_group) AS name
FROM #__jcomments
ORDER BY object_group ASC;

After isolating the queries, our next step was to locate the file containing these queries, and it didn’t take us long: it was the file comments.php located under the administrator/components/com_jcomments/models/ folder. So, all we had to do was to optimize the queries in that file. This was done the following way:

• At the very beginning of the function getListQuery, we added the following code:

  $sql = 'SELECT COUNT(*) FROM ' . $this->_db->quoteName('#__jcomments_reports') . ' AS r WHERE r.commentid = jc.id';
$this->_db->setQuery($sql);
$numReports = $this->_db->loadResult();

  We then changed the following line:

  $reportsSubQuery = ', (SELECT COUNT(*) FROM ' . $this->_db->quoteName('#__jcomments_reports') . ' AS r WHERE r.commentid = jc.id) AS reports';

  to:

  $reportsSubQuery = ', ' . $numReports . ' AS reports';

  This way we split the very heavy query into two queries which dramatically improved the performance (subqueries should always be avoided since they create a huge load on the database server), and by dramatically we mean that the execution time was reduced from 30-60 seconds to a very tiny fraction of a second.

• At the very beginning of the function getFilterLanguages, we added the following code:

  $objLanguage= new stdClass();
$objLanguage->name = 'en-GB';
return (array($objLanguage));

  The 3 lines above ensured that JComments didn’t have to scan all the comments to know the available languages. Since we know that our client’s website only uses English, all we needed to do was to hardcode the returned array to only contain the English language (hence the en-GB in the code). If you’re using Spanish, for example, then you should replace en-GB (in the above code) to es-ES. Note that if you have comments in multiple languages, then you should create an object for each language (e.g. $objLanguage1, $objLanguage2, etc…) and then return an array containing these objects (e.g. return array($objLanguage1, $objLanguage2, …)).

• At the very beginning of the function getFilterObjectGroups, we added the following code:

  $objObjectGroup1= new stdClass();
$objObjectGroup1->name = 'com_content';
$objObjectGroup2= new stdClass();
$objObjectGroup2->name = 'com_k2';
return (array($objObjectGroup1, $objObjectGroup2));

  The above code will work for websites using Joomla’ core and/or K2 for content management (the website we were working on was using both). If you are using another extension for content management in conjunction with JComments, then you should add it to the code above (while, at the same time, removing the unnecessary ones, such as com_k2 if you’re not using K2).

After implementing the above code changes, our client experienced huge performance gains and the MySQL server was relieved from doing unnecessary work.

Now, in case your website is suffering from slowdowns when you’re working with JComments in the backend, then try implementing the above optimizations. If you are still having problems (even after doing the above), or if you just don’t want to touch any code, then please contact us. We will definitely help and we’re confident that you will see huge improvements on your website in very little time and for a very little cost.