I know there are a few posts about slow board. i looked at mystik's post and i changed my database from MyIsam to Inno DB. i checked the queries and this is what i got
SELECT variable, value
FROM smf_settings
in /usr/local/psa/home/vhosts/kevmundial.com/httpdocs/board/Sources/Load.php line 39, which took 0.00618696212769 seconds.
SELECT data
FROM smf_sessions
WHERE session_id = '19c131a32897808787ffd222564e9668'
LIMIT 1
in /usr/local/psa/home/vhosts/kevmundial.com/httpdocs/board/Sources/Load.php line 1174, which took 0.0256180763245 seconds.
SELECT mem.*, IFNULL(a.ID_ATTACH, 0) AS ID_ATTACH
FROM smf_members AS mem
LEFT JOIN smf_attachments AS a ON (a.ID_MEMBER = 1)
WHERE mem.ID_MEMBER = 1
LIMIT 1
in /usr/local/psa/home/vhosts/kevmundial.com/httpdocs/board/Sources/Load.php line 109, which took 0.00696611404419 seconds.
SELECT variable, value, ID_MEMBER, ID_THEME
FROM smf_themes
WHERE ID_MEMBER IN (0, 1)
AND ID_THEME IN (8, 1)
in /usr/local/psa/home/vhosts/kevmundial.com/httpdocs/board/Sources/Load.php line 680, which took 0.0151989459991 seconds.
DELETE FROM smf_log_online
WHERE logTime < NOW() - INTERVAL 1200 SECOND
OR session = '19c131a32897808787ffd222564e9668' OR ID_MEMBER = 1
in /usr/local/psa/home/vhosts/kevmundial.com/httpdocs/board/Sources/Subs.php line 1192, which took 0.00120496749878 seconds.
INSERT IGNORE INTO smf_log_online
(session, ID_MEMBER, ip, url)
VALUES ('19c131a32897808787ffd222564e9668', 1, IFNULL(INET_ATON('207.38.213.249'), 0), 'a:1:{s:10:\"USER_AGENT\";s:84:\"Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.7.5) Gecko/20041107 Firefox/1.0\";}')
in /usr/local/psa/home/vhosts/kevmundial.com/httpdocs/board/Sources/Subs.php line 1196, which took 0.00195693969727 seconds.
SELECT
c.name AS catName, c.ID_CAT, b.ID_BOARD, b.name AS boardName, b.description,
b.numPosts, b.numTopics, b.ID_PARENT,
IFNULL(mem.memberName, m.posterName) AS posterName, m.posterTime, m.subject, m.ID_TOPIC,
IFNULL(mem.realName, m.posterName) AS realName,
(IFNULL(lb.logTime, 0) >= b.lastUpdated) AS isRead, c.canCollapse,
IFNULL(cc.ID_MEMBER, 0) AS isCollapsed,
IFNULL(mem.ID_MEMBER, 0) AS ID_MEMBER, m.ID_MSG,
IFNULL(mem2.ID_MEMBER, 0) AS ID_MODERATOR, mem2.realName AS modRealName
FROM smf_categories AS c, smf_boards AS b
LEFT JOIN smf_messages AS m ON (m.ID_MSG = b.ID_LAST_MSG)
LEFT JOIN smf_members AS mem ON (mem.ID_MEMBER = m.ID_MEMBER)
LEFT JOIN smf_log_boards AS lb ON (lb.ID_BOARD = b.ID_BOARD AND lb.ID_MEMBER = 1)
LEFT JOIN smf_collapsed_categories AS cc ON (cc.ID_CAT = c.ID_CAT AND cc.ID_MEMBER = 1)
LEFT JOIN smf_moderators AS mods ON (mods.ID_BOARD = b.ID_BOARD)
LEFT JOIN smf_members AS mem2 ON (mem2.ID_MEMBER = mods.ID_MEMBER)
WHERE 1
AND b.ID_CAT = c.ID_CAT
AND b.childLevel <= 1
ORDER BY c.catOrder, b.childLevel, b.boardOrder
in /usr/local/psa/home/vhosts/kevmundial.com/httpdocs/board/Sources/BoardIndex.php line 82, which took 3.42907094955 seconds.
SELECT
lo.ID_MEMBER, lo.logTime, mem.realName, mem.memberName, mem.showOnline,
mg.onlineColor, mg.ID_GROUP, mg.groupName
FROM smf_log_online AS lo
LEFT JOIN smf_members AS mem ON (mem.ID_MEMBER = lo.ID_MEMBER)
LEFT JOIN smf_membergroups AS mg ON (mg.ID_GROUP = IF(mem.ID_GROUP = 0, mem.ID_POST_GROUP, mem.ID_GROUP))
in /usr/local/psa/home/vhosts/kevmundial.com/httpdocs/board/Sources/BoardIndex.php line 240, which took 0.285432100296 seconds.
SELECT
m.posterTime, m.subject, m.ID_TOPIC, m.ID_MEMBER, m.ID_MSG,
IFNULL(mem.realName, m.posterName) AS posterName, t.ID_BOARD, b.name AS bName,
m.body, m.smileysEnabled
FROM smf_messages AS m, smf_topics AS t, smf_boards AS b
LEFT JOIN smf_members AS mem ON (mem.ID_MEMBER = m.ID_MEMBER)
WHERE m.ID_MSG >= 645
AND t.ID_TOPIC = m.ID_TOPIC
AND b.ID_BOARD = t.ID_BOARD
AND 1
ORDER BY m.ID_MSG DESC
LIMIT 5
in /usr/local/psa/home/vhosts/kevmundial.com/httpdocs/board/Sources/Recent.php line 88, which took 0.204585075378 seconds.
SELECT code, filename, description
FROM smf_smileys
ORDER BY LENGTH(code) DESC
in /usr/local/psa/home/vhosts/kevmundial.com/httpdocs/board/Sources/Subs.php line 1112, which took 0.00710487365723 seconds.
UPDATE smf_log_activity
SET
hits = hits + 1
WHERE date = 20050223
LIMIT 1
in /usr/local/psa/home/vhosts/kevmundial.com/httpdocs/board/Sources/Subs.php line 1477, which took 0.251266956329 seconds.
Can any tell me what is causing this?
Obviously this query is causing it:
SELECT
c.name AS catName, c.ID_CAT, b.ID_BOARD, b.name AS boardName, b.description,
b.numPosts, b.numTopics, b.ID_PARENT,
IFNULL(mem.memberName, m.posterName) AS posterName, m.posterTime, m.subject, m.ID_TOPIC,
IFNULL(mem.realName, m.posterName) AS realName,
(IFNULL(lb.logTime, 0) >= b.lastUpdated) AS isRead, c.canCollapse,
IFNULL(cc.ID_MEMBER, 0) AS isCollapsed,
IFNULL(mem.ID_MEMBER, 0) AS ID_MEMBER, m.ID_MSG,
IFNULL(mem2.ID_MEMBER, 0) AS ID_MODERATOR, mem2.realName AS modRealName
FROM smf_categories AS c, smf_boards AS b
LEFT JOIN smf_messages AS m ON (m.ID_MSG = b.ID_LAST_MSG)
LEFT JOIN smf_members AS mem ON (mem.ID_MEMBER = m.ID_MEMBER)
LEFT JOIN smf_log_boards AS lb ON (lb.ID_BOARD = b.ID_BOARD AND lb.ID_MEMBER = 1)
LEFT JOIN smf_collapsed_categories AS cc ON (cc.ID_CAT = c.ID_CAT AND cc.ID_MEMBER = 1)
LEFT JOIN smf_moderators AS mods ON (mods.ID_BOARD = b.ID_BOARD)
LEFT JOIN smf_members AS mem2 ON (mem2.ID_MEMBER = mods.ID_MEMBER)
WHERE 1
AND b.ID_CAT = c.ID_CAT
AND b.childLevel <= 1
ORDER BY c.catOrder, b.childLevel, b.boardOrder
So... try this:
EXPLAIN SELECT
c.name AS catName, c.ID_CAT, b.ID_BOARD, b.name AS boardName, b.description,
b.numPosts, b.numTopics, b.ID_PARENT,
IFNULL(mem.memberName, m.posterName) AS posterName, m.posterTime, m.subject, m.ID_TOPIC,
IFNULL(mem.realName, m.posterName) AS realName,
(IFNULL(lb.logTime, 0) >= b.lastUpdated) AS isRead, c.canCollapse,
IFNULL(cc.ID_MEMBER, 0) AS isCollapsed,
IFNULL(mem.ID_MEMBER, 0) AS ID_MEMBER, m.ID_MSG,
IFNULL(mem2.ID_MEMBER, 0) AS ID_MODERATOR, mem2.realName AS modRealName
FROM smf_categories AS c, smf_boards AS b
LEFT JOIN smf_messages AS m ON (m.ID_MSG = b.ID_LAST_MSG)
LEFT JOIN smf_members AS mem ON (mem.ID_MEMBER = m.ID_MEMBER)
LEFT JOIN smf_log_boards AS lb ON (lb.ID_BOARD = b.ID_BOARD AND lb.ID_MEMBER = 1)
LEFT JOIN smf_collapsed_categories AS cc ON (cc.ID_CAT = c.ID_CAT AND cc.ID_MEMBER = 1)
LEFT JOIN smf_moderators AS mods ON (mods.ID_BOARD = b.ID_BOARD)
LEFT JOIN smf_members AS mem2 ON (mem2.ID_MEMBER = mods.ID_MEMBER)
WHERE 1
AND b.ID_CAT = c.ID_CAT
AND b.childLevel <= 1
ORDER BY c.catOrder, b.childLevel, b.boardOrder
-[Unknown]
this is what i got when i ran that querty
(https://www.simplemachines.org/community/proxy.php?request=http%3A%2F%2Fkevmundial.com%2Fimages%2Ftableinfo.GIF&hash=430079eb3335543c85964adaea92c8d8d8273e11)
How to make the test queries ? :)
What version of MySQL is this?
-[Unknown]
i have version 4.1.7
phpinfo = http://kevmundial.com/phpinfo.php
I'm not sure what to tell you; that query shouldn't be slow. I would guess that your server is being slowed down by other things.
-[Unknown]
Lainaus käyttäjältä: [Unknown] - helmikuu 26, 2005, 09:21:12 IP
I'm not sure what to tell you; that query shouldn't be slow. I would guess that your server is being slowed down by other things.
-[Unknown]
ask you host about cronjobs as these can greatly slow down the server.