Question Query taking a long time

Thank you, core. I understand what's involved.

I wanted to further clarify on this issue.

1. We've done several other updates to our site ( www.spellingcity.com ) so it is possible that this is not the only contributing factor to the high CPU utilization on the data server. I have found another bottleneck that has to do with some custom code that we updated.

2. After implementing the additional index, for now at least, this query is no longer showing up in the "slow queries" log file of mySQL.

In reference to table / index modification, there's no question that the developers of Joomla and Kunena have done a tremendous job in putting these 2 products together, however, when developing these sort of products for the general market, one can never tell the impact that the architecture will have in every environment. From time to time it is necessary to examine causes of bottlenecks and possible fixes. When looking at these options, I prefer to optimize at the database level rather than the core Joomla code level as the implications could be much more significant.

Adding an index to a table, should not break the system, only speed queries up (or potentially slow them down, depending on the complexity of the index). The idea of adding an index to this table, specifically on the "value" field is rooted in the structure of the query which ultimately has a table.value = 'xxxxx' where clause. In my opinion, anytime you have a where clause on a single field value, a direct (unique or normal index) goes a long way in speeding things up. There is a combination index already on this table that incorporates the "value" field but that doesn't seem to be used by the mySQL query optimizer as its searching 200K+ records to retrieve 1 row!

Adding this index, greatly reduced the number of records searched and the query appears to now be executing in under 1 second (compared to 6 seconds), so I think that's a pretty good improvement.

Would love to hear more specific feedback? I do believe that this is a Joomla deficiency(in 1.0.15 database structure design) which may not exist in 1.5+. However, I don't understand why Kunena needs to execute this query so many times for its forum.

FYI - there is a Joomla performance site that has some interesting information: www.joomlaperformance.com , in particular, check out: www.joomlaperformance.com/articles/perfo...as_queries_4_14.html which suggests adding certain indices to the architecture.

We've manage to improve many aspects of the site and optimize other queries but I still would like to understand if there's a way to speed this one up?

Enable query cache in MySQL, it should help a bit.

I'm just about to fix related (ACL) bug for Kunena 1.0.10, so I may as well look at this issue. It's legacy feature from FB/JoomlaBoard: every dev has made his own ACL checks..

PS. How many entries do you have in that table? I have 10 000 registered users in my Forum and I have no issues with the speed of ACL!

I'm quite good in DB optimization and I have a custom version of Kunena with some optimized queries -- but I never saw ACL (J!1.5) as a problem.

BTW: Many of the proposals in www.joomlaperformance.com for FireBoard seems to slow down Kunena on default_ex template.

Thank you so much for the reply. Let me try and comment on a few of your items.

1. SQL CACHE is enabled, but maybe I need to check into the server settings more specifically. Do you have any particular suggestions as to which mysql settings I should look at and what their values should be?

2. Regarding the ACL check, I think what you're saying is that you're currently using the built in Joomla API (in our case, 1.0.15) and that in other components, developers opted for their own ACL check? I think that would be an excellent idea. This way if we wanted to change the ACL check within Kunena, it wouldn't impact the core Joomla API which may effect other things. I think this is a great idea.

3. We have 130,000 (and growing) Joomla registrations (and hence 130,000 potential forum users. The issue is that this ACL query apparently (at least according to the slow queries mysql logs) examines ALL records to return the result which of course is a problem, especially if it is called many times. The interesting thing is that I looked at the "explain" log for this query in Navicat which further breaks down the actual rows examined per join. Accordingly, one of the joins examines 11 rows and the other examines 25,000 rows (approximately). This is why the slow query log is showing 260,000 (approx) rows examined (11 * 25,000).

4. Regarding the template, are you suggesting that using a different template may improve performance? If so, which template do you recommend?

This query is still a problem, but we've managed to optimize other elements and so its less of an issue, but as we continue to grow, this will be something that we have to deal with very quickly. I'm probably going to be looking at it again this coming week.

1. Go to phpMyAdmin frontpage and try to find runtime information (or similar with traffic lights). It has a lot of information in there -- look at red ones and values in Query Cache: Qcache_*

2. We will replace ACL check soon with better solution, but unfortunately not for Kunena 1.0.10 or 1.5.1.

3. Couldn't you add proper indexes into acl table? It would solve the problem permanently. I don't have a place where I can debug this one, so there's nothing else than I can do than to take a look at your MySQL server.

4. Templates do not help, as they all contain the same checks.