Hello,
I am using the JComments 2.2.0.0 [13/05/2010] with latest comments module. I have huge database of comments (about 90 000) on my web transfered from my old Mambo site. Everything works fine, but when I use the latest comment module, I have a lot issues in my MYSQL slow log. I think that module looks up through the whole database to find the last seven comments, and It causes the problem, because database is too big.
However, I am not able to find a way to fix this. I am not a programmer, so I tried to politely ask, if someone doesnt know the solution for this. I think there could be way to force the module to look only in - for example - one week old comments? This could reduce the number of queries and could make this module usable even for big amounts of comment?
Would there be anyone who could help? This would be great! Thanks in advance!
Down there is typical problem in MYSQL slow log for this issue:
# Time: 101020 12:06:00
# User@Host: silvarium1[silvarium1] @ localhost []
# Query_time: 11 Lock_time: 0 Rows_sent: 7 Rows_examined: 84954
SELECT c.id AS id, c.title AS title, c.sectionid, CASE WHEN CHAR_LENGTH(c.alias) THEN CONCAT_WS(":", c.id, c.alias) ELSE c.id END as slug, CASE WHEN CHAR_LENGTH(ct.alias) THEN CONCAT_WS(":", ct.id, ct.alias) ELSE ct.id END as catslug
, COUNT(cc.id) AS comments, MAX(cc.date) AS commentdate
FROM jos_content AS c
LEFT JOIN jos_jcomments AS cc ON c.id = cc.object_id
LEFT JOIN jos_categories AS ct ON ct.id = c.catid
WHERE c.state = 1
AND c.access <= '0'
AND (c.publish_up = '0000-00-00 00:00:00' OR c.publish_up <= '2010-10-20 10:05:49')
AND (c.publish_down = '0000-00-00 00:00:00' OR c.publish_down >= '2010-10-20 10:05:49')
AND cc.published = 1
AND cc.object_group = 'com_content'
GROUP BY c.id, c.title, c.sectionid, slug, catslug
ORDER BY commentdate DESC
LIMIT 7;
# User@Host: silvarium1[silvarium1] @ localhost []
# Query_time: 11 Lock_time: 0 Rows_sent: 7 Rows_examined: 84954
SELECT c.id AS id, c.title AS title, c.sectionid, CASE WHEN CHAR_LENGTH(c.alias) THEN CONCAT_WS(":", c.id, c.alias) ELSE c.id END as slug, CASE WHEN CHAR_LENGTH(ct.alias) THEN CONCAT_WS(":", ct.id, ct.alias) ELSE ct.id END as catslug
, COUNT(cc.id) AS comments, MAX(cc.date) AS commentdate
FROM jos_content AS c
LEFT JOIN jos_jcomments AS cc ON c.id = cc.object_id
LEFT JOIN jos_categories AS ct ON ct.id = c.catid
WHERE c.state = 1
AND c.access <= '0'
AND (c.publish_up = '0000-00-00 00:00:00' OR c.publish_up <= '2010-10-20 10:05:49')
AND (c.publish_down = '0000-00-00 00:00:00' OR c.publish_down >= '2010-10-20 10:05:49')
AND cc.published = 1
AND cc.object_group = 'com_content'
GROUP BY c.id, c.title, c.sectionid, slug, catslug
ORDER BY commentdate DESC
LIMIT 7;
JComments component 