FUDforum
Fast Uncompromising Discussions. FUDforum will get your users talking.
Home » General » Database discussions » Query taking 2 minutes (Board grinds to halt for that period of time)
Show: Today's Messages :: Polls :: Message Navigator
Return to the default flat view Create a new topic Submit Reply
Query taking 2 minutes [message #161905] Fri, 19 February 2010 15:33 Go to previous message
P_G_ is currently offline  P_G_
Messages: 9
Registered: February 2010
Karma:
Junior Member
Not strictly an installation issue, but not sure where this should go.

We are experiencing a few occasions during a day where the forum grinds to a halt due to locked queries. It is down to the query below, which takes anything between 85 and 120 seconds to run.

First off, when is it run, and is it something which can be disabled via a feature? Or is there an alternative way we can solve it or get around it?

It brings back around 15,000 records from the thread table.

Thanks
P_G_

SELECT
m.*, COALESCE(m.flag_cc, u.flag_cc) AS flag_cc, COALESCE(m.flag_country, u.flag_country) AS flag_country,
t.thread_opt, t.root_msg_id, t.last_post_id, t.forum_id,
f.message_threshold, f.name,
u.id AS user_id, u.alias AS login, u.avatar_loc, u.email, u.posted_msg_count, u.join_date, u.location,
u.sig, u.custom_status, u.icq, u.jabber, u.affero, u.aim, u.msnm, u.yahoo, u.skype, u.google, u.last_visit AS time_sec, u.users_opt,
l.name AS level_name, l.level_opt, l.img AS level_img,
p.max_votes, p.expiry_date, p.creation_date, p.name AS poll_name, p.total_votes,
pot.id AS cant_vote,
r.last_view,
mm.id AS md,
m2.subject AS thr_subject,
COALESCE(g2.group_cache_opt, g1.group_cache_opt) AS group_cache_opt
FROM
fud26_msg m
INNER JOIN fud26_thread t ON m.thread_id=t.id
INNER JOIN fud26_msg m2 ON m2.id=t.root_msg_id
INNER JOIN fud26_forum f ON t.forum_id=f.id
INNER JOIN fud26_cat c ON f.cat_id=c.id


INNER JOIN fud26_group_cache g1 ON g1.user_id=2147483647 AND g1.resource_id=f.id LEFT JOIN fud26_group_cache g2 ON g2.user_id=6927 AND g2.resource_id=f.id
LEFT JOIN fud26_read r ON r.thread_id=t.id AND r.user_id=6927
LEFT JOIN fud26_users u ON m.poster_id=u.id
LEFT JOIN fud26_level l ON u.level_id=l.id
LEFT JOIN fud26_poll p ON m.poll_id=p.id
LEFT JOIN fud26_poll_opt_track pot ON pot.poll_id=p.id AND pot.user_id=6927
LEFT JOIN fud26_mod mm ON mm.forum_id=f.id AND mm.user_id=6927
WHERE
m.apr=1

AND f.id=1


AND m.post_stamp > 1235529738 AND (r.id IS NULL OR r.last_view < m.post_stamp)
AND (mm.id IS NOT NULL OR (COALESCE(g2.group_cache_opt, g1.group_cache_opt) & 2) > 0)
ORDER BY
f.last_post_id ASC , t.last_post_date ASC , m.post_stamp ASC
LIMIT 0,50;

Report message to a moderator

[Message index]
 
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Previous Topic: Cannot connect to database
Next Topic: Has anyone tried to use this w/SQL Server 2000
Goto Forum:
  

-=] Back to Top [=-
[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu Nov 28 01:09:08 GMT 2024

Total time taken to generate the page: 0.04127 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.1.3.
Copyright ©2001-2023 FUDforum Bulletin Board Software