Hi there,
i'm facing a performance issue with Kunena 1.5.4. This is an upgraded Kunena from Fireboard (Joomla 1.0.x) -> Kunena 1.0.11 (Joomla 1.0.x) -> Kunena 1.54 (Joomla 1.5.x). The migration worked fine so far, i have no problems with functionality.
Overall, the Forum is super fast. It's hosted on a dedicated server with enough resources. BUT: if i'm in the categorie overview, and want to switch to a specific categorie (f.e. 400 topics, 9000 replies) it takes foreeeeeeever to finish the load (ok, about 1 minute). CPU load (mysqld) is at 100% in this time...
The strange thing about this: if i switch back to the overview and click again in the same categorie, it goes super fast. Seem's like mysql caches the result for a moment, and therefore gives me the fast response the second time.
Investigating the mysql-slow.log file tells me, that the query in charge is this one:
Code:
SELECT
a.*,
t.mesid,
t.message AS messagetext,
m.mesid AS attachmesid,
(f.thread>0) AS myfavorite,
u.avatar,
MAX(b.time) AS lastpost
FROM jos_fb_messages AS a
JOIN jos_fb_messages_text AS t ON a.thread = t.mesid
LEFT JOIN jos_fb_messages AS b ON b.thread = a.thread
LEFT JOIN jos_fb_attachments AS m ON m.mesid = a.id
LEFT JOIN jos_fb_favorites AS f ON f.thread = a.id && f.userid='331'
LEFT JOIN jos_comprofiler AS u ON u.user_id = a.userid
WHERE a.parent = '0'
AND a.catid = '8'
AND a.hold = '0'
GROUP BY id
ORDER BY ordering DESC, lastpost DESC LIMIT 0, 20;
Time taken:
Code:
Status Dauer
starting 0.000017
checking query cache for query 0.000139
Opening tables 0.000025
System lock 0.000008
Table lock 0.000061
init 0.000055
optimizing 0.000021
statistics 0.000138
preparing 0.000030
Creating tmp table 0.000259
executing 0.000004
[b]Copying to tmp table 54.548688[/b]
Sorting result 0.001145
Sending data 0.000623
end 0.000004
removing tmp table 0.000517
end 0.000007
query end 0.000009
freeing items 0.000058
storing result in query cache 0.000011
logging slow query 0.000002
logging slow query 0.000058
cleaning up 0.000029
Am i missing some sort of indexes or so in the database? How else is this arguable? Does anyone have a tip on this one?
Best regards,
failbit
