Question Query taking a long time

Hi All,

We recently upgraded from Fireboard to Kunena 1.08 and now 1.09. Post upgrade, our dedicated mySQL server started "choking" meaning CPU utilization is continually above 50% (normally below 20%) and for a high traffic website such as ours, this is a problem.

In looking at the "slow queries" log for mySQL I noticed this query as the primary cause of the problem. This query is executed many times.

# Query_time: 6 Lock_time: 0 Rows_sent: 1 Rows_examined: 260619
SELECT g.*
FROM jos_core_acl_aro_groups AS g
INNER JOIN jos_core_acl_groups_aro_map AS gm ON gm.group_id = g.group_id
INNER JOIN jos_core_acl_aro AS ao ON ao.aro_id = gm.aro_id
WHERE ao.value='[SOME_NUMBER]';

I searched the Joomla configuration files and found that this query is executed by the _getGroup function in "gacl.api.class.php" in the \includes directory of Joomla. This API function is internal to the class and is called by public function "getAroGroup" in the same file.

The public "getAroGroup" is called in very few places, as follows:

/includes/joomla.php:
$grp = $acl->getAroGroup( $row->id );

/components/com_kunena/template/default/post.php:
$_arogrp = $acl->getAroGroup($subs->id);

/components/com_kunena/kunena.php:
$aro_group = $acl->getAroGroup($my->id);

As we didn't have this issue before the upgrade, I believe that the problems are created by Kunena's usage of this function call which of course is exponentially increased by the slowness of the response.

I'm looking for 2 possible fixes:

1. Is Kunena correctly using this function (i.e. is it necessary to call it as many times as it does or maybe there's a better way)?

2. If this is not a Kunana problem, i.e. the Joomla query needs to be more efficient, does anyone know how by using indexing or other means to make this query run more quickly?

I'm posting this on the Kunena forum b/c this problem didn't exist prior to the installation of Kunena. The query may have been slow, but it wasn't running as many times as Kunena is running it so it wasn't an issue.

Many thanks!

Just wanted to provide an updated... I added a "value_only" index to the jos_core_acl_aro table against the "value" field. This seemed to speed things up a bit. I have to wait about 24 hours to asses impact.

Thank you for the work you've done to examine Kunena SQL performance. I'm very interested to see if the changes you've made make any difference to query execution times.

To help me better understand what you've written, I'm attaching a screenshot that illustrates the area we're discussing. Can you tell me, please, how to set the "value_only" index attribute to the value column?

image missing http://www.kunena.com/media/kunena/attachments/legacy/images/core_acl_aro_tableSchema.jpg

Sozzled: I don't think the information you seek is availably in your attached picture. (A picture doesn't always tell more than words.)

If you consider altering the underlying DB structure of Joomla (or Kunena) you must be an SQL expert (or guru) and understand way the developers have chosen a specific structure and how your change affect the code utilizing the chosen DB structure.

If you want to learn more about SQL, www.mysql.com have lots of documentation.
Some starting point about indices:
dev.mysql.com/doc/refman/5.1/en/create-index.html
dev.mysql.com/doc/refman/5.1/en/mysql-indexes.html

Thanks, core. (Yes, I agree, pictures only help to explain ... they're not explanations in themselves. ) I'm reasonably proficient with common usages of database design and querying but the term "value only" (in connection with a table index) has me completely perplexed. Care to comment?

I'm not familiar with the software you are using, but my guess is that you need to go into the left tab in the lower part. I only use text based SQL tools, but I will PM you an example.