Solved K 3.0.2 with many user records slow due to bad query

The only thing i have edited in the configuration report is two email adresses which i do not want to appear on a publicly readable forum. Maybe i am a little exagerating here, but i get enough spam already.

Yes, the site lives on my intranet only. I have a Joomla 1.0 site using the old JoomlaBoard with 10000 users in it on the internet. I copied it to my intranet and started the upgrade process to finally get to joomla 2.5 using kunena 2.0.4. I used J2XML to transfer the users information from Joomla 1.5 to Joomla 2.5.

Yesterday i clicked the "update" button in the controlpanel which took me to kunena 3.0.2.
I snooped around the new version of kunena and came across the problem discussed in this thread.

This kind of operation i do not do on live internet of course.

I will get my joomla2.5/kunena 2.0.4 back from my backup tapes, put it on my intranet side by side with the kunena 3.0.2 version and do some more testing / comparing.

back soon

johanst wrote: The site lives on my intranet only.

Please see Support for PC-hosted websites (e.g. WAMP, XAMPP) ; the same policy applies to the support we can give to sites hidden behind corporate/intranet firewalls.

From the sounds of this, this is not a problem specifically caused by Kunena but, rather, a problem that may have been cascaded by the procedure you used in evolving an old J! 1.0 site, with a forum that was a forerunner of Fireboard or Kunena (I'm not sure) and the SQL problems may have compounded themselves over the iterations involved in the overall migration process. For that reason and, because we cannot corroborate the problem having it's root cause in Kunena, I am moving this topic to a different category where others may be able to offer their insights to you about your specific SQL database performance problem. We wish you good luck In resolving this particular problem.

I think i have found the cause for the problem now.

I revived my joomla.25 / Kunena 2.0 website from the backups, and in that configuration, getting the users list was fast. With debugging on, i could see that an INNER JOIN was used on tables #__users and #__kunena_users.

After upgrading to 3.0.2, getting the users list was slow.
In 3.0.2, a LEFT JOIN query is used instead of an INNER JOIN.
Playing with this in a mysql client shows that the LEFT JOIN query is way slower than the INNER JOIN query.

I manually added an index on the userid field, and now the LEFT JOIN query was roughly as fast as the INNER JOIN query.

I took a brandnew joomla 2.5.14 website and installed fresh Kunena 3.0.2 on it. In this configuration, the userid field is declared as the PRIMARY KEY of table #__kunena_users, and so it is indexed by default.

Finally i found that during the migration from Joomla 1.0 to Joomla 1.5, the PRIMARY KEY declaration on the userid field disappeared in my database. Probably my own fault.
After the switch from INNER JOIN to LEFT JOIN, this became a problem.

You can kill this thread now.

When i do SHOW INDEX FROM `o5wab_kunena_users` , i obtain that :

Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment
o5wab_kunena_users 0 PRIMARY 1 userid A 2 NULL NULL BTREE
o5wab_kunena_users 1 group_id 1 group_id A 2 NULL NULL YES BTREE
o5wab_kunena_users 1 posts 1 posts A 2 NULL NULL YES BTREE
o5wab_kunena_users 1 uhits 1 uhits A 2 NULL NULL YES BTREE
o5wab_kunena_users 1 banned 1 banned A 2 NULL NULL YES BTREE
o5wab_kunena_users 1 moderator 1 moderator A 2 NULL NULL YES BTREE

I think there is something missing during upgrading