Question Query taking a long time

I like your suggestion of adding indexes, I think its really the way to go. The problem is that I've tried adding indexes, but for some reason, they have no impact on the query, so I'm obviously not adding the right indexes, or something along those lines. Do you have any suggestions as to what the correct indexing structure for the tables involved in this query should be?

Also, I think our mySQL configuraiton is setup pretty OK for now, parameters and settings are below (if you see something unusual, let me know):

query_alloc_block_size: 8192
query_cache_limit: 4194304
query_cache_min_res_unit: 4096
query_cache_size: 134217728
query_cache_type: ON
query_cache_wlock_invalidate: OFF
query_prealloc_size: 8192

Thanks

I don't have large userbase on my Joomla 1.0 test site, so just give me the slow query and output from EXPLAIN?

Your query cache is ok and it should usually catch most queries, but for some reason it doesn't work for that query..?

Hey... thanks for your ongoing support, really appreciate it.

Ok, so I spent some more time on this issue today with a pair of fresh eyes and I think that I solved it (hopefully).

This was resolved by adding the "aro_id" index (for only the "aro_id") field of the "jos_core_acl_groups_aro_map" table.

The query now looks at "3" rows instead of 270,000+, big improvement .

I just want to reiterate to ALL that this is NOT a Kunena issue. This is a Joomla table indexing issue, that specifically comes up when using Joomla 1.0.15 (probably earlier versions as well) on sites with a LARGE number of registrations.

Again, thanks!

Could you write this Joomla fix into our Documentation (wiki)?

And good that this one was solved.

Sure, where would you like me to post this in the Wiki?

Under category: Tips and tricks

docs.kunena.com/index.php/Category:Tips_and_tricks