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

Hi,
i have 10000 user records in my joomla/kunena website.
after upgrading from kunena 2.0 to 3.0.2, showing the list of users in kunena admin brings mysql server to 100% for about 5 minutes.

debug on revealed this query:

SELECT a.*,ku.*
FROM #__users AS a
LEFT JOIN #__kunena_users AS ku ON a.id=ku.userid
ORDER BY a.username ASC

that is 10000 x 10000 records in my database!

see class KunenaAdminModelUsers -> getListQuery()

limiting the query won't help, according to mysql EXPLAIN:

mysql> explain SELECT a.*,ku.* FROM uvniu_users AS a LEFT JOIN uvniu_kunena_users AS ku ON a.id=ku.userid ORDER BY a.username ASC limit 1, 100;

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
| 1 | SIMPLE | a | ALL | NULL | NULL | NULL | NULL | 9007 | Using temporary; Using filesort |
| 1 | SIMPLE | ku | ALL | NULL | NULL | NULL | NULL | 9400 | |
2 rows in set (0.00 sec)

also omitting the sorting has no effect.

Can it be avoided to use the LEFT JOIN on these tables?

Hello,

Thanks for the report, can-you put here please your Kunena report configuration settings ?

The following query is executed only one time, so it's not that which make slowing the render of the page :

I will investigate on the subject

Something for your guys to think about: there are 3½ times that number of users here at www.kunena.org (> 35,000 users) and producing a user list takes a fraction of a second. I think there's something else going on here and johant's configuration report may provide us with the necessary clues.

here is my report config:

Database collation check: The collation of your table fields are correct

Joomla! SEF: Enabled | Joomla! SEF rewrite: Disabled | FTP layer: Disabled |

This message contains confidential information

htaccess: Exists | PHP environment: Max execution time: 30 seconds | Max execution memory: 128M | Max file upload: 8M

Kunena menu details:

Warning: Spoiler!

ID	Name	Menutype	Link	Path
166	Forum	kunenamenu	view=home&defaultmenu=168	forum
167	Index	kunenamenu	view=category&layout=list	forum/index
168	Recent Topics	kunenamenu	view=topics&mode=replies	forum/recent
169	New Topic	kunenamenu	view=topic&layout=create	forum/newtopic
170	No Replies	kunenamenu	view=topics&mode=noreplies	forum/noreplies
171	My Topics	kunenamenu	view=topics&layout=user&mode=default	forum/mylatest
172	Profile	kunenamenu	view=user	forum/profile
173	Help	kunenamenu	view=misc	forum/help
174	Search	kunenamenu	view=search	forum/search
175	Forum	mainmenu	Itemid=166	kunena-2013-10-04

Joomla default template details : fmk | author: | version: 2.5.0 | creationdate: Unknown

Kunena default template details : Blue Eagle 2.0 | author: Kunena Team | version: 3.0.2 | creationdate: 2013-08-18

Kunena version detailed: Kunena 3.0.2 | 2013-08-18 [ Nocturne ]
| Kunena detailed configuration:

Warning: Spoiler!

Kunena config settings:
board_offline	0
enablerss	1
threads_per_page	20
messages_per_page	6
messages_per_page_search	15
showhistory	1
historylimit	6
shownew	1
disemoticons	0
template	blue_eagle
showannouncement	1
avataroncat	0
catimagepath	category_images
showchildcaticon	0
rtewidth	450
rteheight	300
enableforumjump	0
reportmsg	0
username	1
askemail	0
showemail	0
showuserstats	0
showkarma	0
useredit	1
useredittime	0
useredittimegrace	600
editmarkup	1
allowsubscriptions	1
subscriptionschecked	1
allowfavorites	0
maxsubject	50
maxsig	300
regonly	0
pubwrite	0
floodprotection	0
mailmod	0
mailadmin	0
captcha	0
mailfull	1
allowavatarupload	1
allowavatargallery	1
avatarquality	75
avatarsize	2048
imageheight	800
imagewidth	800
imagesize	150
filetypes	txt,rtf,pdf,zip,tar.gz,tgz,tar.bz2
filesize	120
showranking	0
rankimages	0
userlist_rows	30
userlist_online	1
userlist_avatar	0
userlist_name	0
userlist_posts	1
userlist_karma	0
userlist_email	0
userlist_joindate	1
userlist_lastvisitdate	1
userlist_userhits	0
latestcategory	4,2,3,5,8
showstats	0
showwhoisonline	0
showgenstats	0
showpopuserstats	0
popusercount	5
showpopsubjectstats	0
popsubjectcount	5
usernamechange	0
showspoilertag	1
showvideotag	1
showebaytag	1
trimlongurls	1
trimlongurlsfront	40
trimlongurlsback	20
autoembedyoutube	1
autoembedebay	1
ebaylanguagecode	en-us
sessiontimeout	1800
highlightcode	0
rss_type	topic
rss_timelimit	month
rss_limit	100
rss_included_categories
rss_excluded_categories
rss_specification	rss2.0
rss_allow_html	1
rss_author_format	name
rss_author_in_title	1
rss_word_count	0
rss_old_titles	1
rss_cache	900
defaultpage	recent
default_sort	asc
sef	1
showimgforguest	1
showfileforguest	1
pollnboptions	4
pollallowvoteone	1
pollenabled	0
poppollscount	5
showpoppollstats	0
polltimebtvotes	00:15:00
pollnbvotesbyuser	100
pollresultsuserslist	1
maxpersotext	50
ordering_system	mesid
post_dateformat	ago
post_dateformat_hover	datetime
hide_ip	1
imagetypes	jpg,jpeg,gif,png
checkmimetypes	1
imagemimetypes	image/jpeg,image/jpg,image/gif,image/png
imagequality	50
thumbheight	32
thumbwidth	32
hideuserprofileinfo	put_empty
boxghostmessage	0
userdeletetmessage	0
latestcategory_in	1
topicicons	0
debug	0
catsautosubscribed	0
showbannedreason	0
version_check	1
showthankyou	0
showpopthankyoustats	0
popthankscount	5
mod_see_deleted	0
bbcode_img_secure	text
listcat_show_moderators	0
lightbox	1
show_list_time	0
show_session_type	2
show_session_starttime	60
userlist_allowed	1
userlist_count_users	1
enable_threaded_layouts	0
category_subscriptions	post
topic_subscriptions	every
pubprofile	0
thankyou_max	10
email_recipient_count	0
email_recipient_privacy	bcc
captcha_post_limit	0
keywords	0
userkeywords	0
image_upload	registered
file_upload	registered
topic_layout	flat
time_to_create_page	1
show_imgfiles_manage_profile	1
hold_newusers_posts	0
hold_guest_posts	0
attachment_limit	8
pickup_category	0
article_display	intro
send_emails	0
fallback_english	1
cache	1
cache_time	60
iptracking	1
rss_feedburner_url
autolink	1
access_component	1
userlist_usertype	0
sefutf8	0
default_view	flat
enableRSS	1
enablePDF	1
showHistory	1
historyLimit	6
showNew	1
newChar	NEW!
jmambot	0
templateimagepath	default
joomlaStyle	0
showAnnouncement	1
avatarOnCat	0
CatImagePath	category_images/
numchildcolumn	2
showChildCatIcon	1
annmodid	62
enableRulesPage	0
enableForumJump	1
postStats	1
statsColor	9
usereditTime	0
usereditTimeGrace	600
editMarkUp	1
wrap	250
maxSubject	50
maxSig	300
changename	0
allowAvatar	1
allowAvatarUpload	1
allowAvatarGallery	1
imageProcessor	gd2
avatarSmallHeight	50
avatarSmallWidth	50
avatarHeight	100
avatarWidth	100
avatarLargeHeight	250
avatarLargeWidth	250
avatarQuality	75
avatarSize	2048
allowImageUpload	0
allowImageRegUpload	1
imageHeight	499
imageWidth	499
imageSize	50
allowFileUpload	0
allowFileRegUpload	1
fileTypes	zip,txt,doc,gz
fileSize	120
cb_profile	0
badwords	0
discussBot	0
userlist_username	1
userlist_group	0
showLatest	1
latestCount	10
latestCountPerPage	5
latestSingleSubject	1
latestReplySubject	1
latestSubjectLength	100
latestShowDate	1
latestShowHits	1
latestShowAuthor	1
showStats	1
showWhoisOnline	1
showGenStats	1
showPopUserStats	1
PopUserCount	5
showPopSubjectStats	1
PopSubjectCount	5
rules_infb	0
rules_link	www.bestofjoomla.com/
enableHelpPage	0
help_infb	0
help_link	www.bestofjoomla.com/

| Kunena integration settings:

Warning: Spoiler!

Kunena - AlphaUserPoints Disabled
Kunena - Community Builder Disabled
Kunena - Gravatar Disabled
Kunena - JomSocial Disabled
Kunena - Joomla Enabled: access=1 login=1
Kunena - Kunena Enabled: avatar=1 profile=1
Kunena - UddeIM Disabled

| Joomla! detailed language files installed:

Warning: Spoiler!

Joomla! languages installed:
en-GB	English (United Kingdom)

Third-party components: None

Third-party SEF components: None

Plugins: None

Modules: None

on the way home i realized that knowing what indexes have been set on the two database tables involved might also be of importance:

mysql> show index from uvniu_users;
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
| uvniu_users | 0 | PRIMARY | 1 | id | A | 9007 | NULL | NULL | | BTREE | |
| uvniu_users | 1 | usertype | 1 | usertype | A | 19 | NULL | NULL | | BTREE | |
| uvniu_users | 1 | idx_name | 1 | name | A | 9007 | NULL | NULL | | BTREE | |
| uvniu_users | 1 | idx_block | 1 | block | A | 39 | NULL | NULL | | BTREE | |
| uvniu_users | 1 | username | 1 | username | A | 9007 | NULL | NULL | | BTREE | |
| uvniu_users | 1 | email | 1 | email | A | 9007 | NULL | NULL | | BTREE | |
6 rows in set (0.00 sec)

and

mysql> show index from uvniu_kunena_users;
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
| uvniu_kunena_users | 1 | group_id | 1 | group_id | A | 1 | NULL | NULL | YES | BTREE | |
| uvniu_kunena_users | 1 | posts | 1 | posts | A | 59 | NULL | NULL | YES | BTREE | |
| uvniu_kunena_users | 1 | uhits | 1 | uhits | A | 1 | NULL | NULL | YES | BTREE | |
| uvniu_kunena_users | 1 | banned | 1 | banned | A | 9400 | NULL | NULL | YES | BTREE | |
| uvniu_kunena_users | 1 | moderator | 1 | moderator | A | 2 | NULL | NULL | YES | BTREE | |
5 rows in set (0.00 sec)

i will have a look myself too at these. thanks for your quick responses.

johan

might it be that table uvniu_kunena_users is missing an index on userid field?

Where does this website of yours exist? The site is not on the internet and it looks like you may have edited your configuration report to remove certain information that might point to the cause of the proble; it's hard to say. If this site exists on a private internet or on your own PC then the reason for the poor performance may have something to do with the way that your SQL server is configured.

It is also possible that your database indexes have been corrupted. I really do not know by only looking at what information you have given us in your configuration report.