FUDforum: Database discussions » Query taking 2 minutes

Home » General » Database discussions » Query taking 2 minutes (Board grinds to halt for that period of time)

Show: Today's Messages :: Polls :: Message Navigator

Query taking 2 minutes [message #161905]

Fri, 19 February 2010 15:33

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]

		Query taking 2 minutes By: P_G_ on Fri, 19 February 2010 15:33
		Re: Query taking 2 minutes By: naudefj on Fri, 19 February 2010 19:58
		Re: Query taking 2 minutes By: P_G_ on Fri, 19 February 2010 20:09
		Re: Query taking 2 minutes By: naudefj on Fri, 19 February 2010 20:54
		Re: Query taking 2 minutes By: P_G_ on Sat, 20 February 2010 12:54
		Re: Query taking 2 minutes By: naudefj on Sun, 21 February 2010 00:08
		Re: Query taking 2 minutes By: P_G_ on Mon, 22 February 2010 10:57
		Re: Query taking 2 minutes By: naudefj on Mon, 22 February 2010 16:06
		Re: Query taking 2 minutes By: P_G_ on Mon, 22 February 2010 17:18
		Re: Query taking 2 minutes By: naudefj on Tue, 23 February 2010 19:19
		Re: Query taking 2 minutes By: Ernesto on Wed, 24 November 2010 10:34
		Re: Query taking 2 minutes By: Ernesto on Wed, 24 November 2010 11:23
		Re: Query taking 2 minutes By: Ernesto on Wed, 24 November 2010 11:25
		Re: Query taking 2 minutes By: Ernesto on Wed, 24 November 2010 12:01
		Re: Query taking 2 minutes By: Ernesto on Wed, 24 November 2010 12:04
		Re: Query taking 2 minutes By: Ernesto on Wed, 24 November 2010 12:21
		Re: Query taking 2 minutes By: Ernesto on Wed, 24 November 2010 12:30
		Re: Query taking 2 minutes By: naudefj on Wed, 24 November 2010 12:47
		Re: Query taking 2 minutes By: Ernesto on Wed, 24 November 2010 13:35
		Re: Query taking 2 minutes By: Ernesto on Wed, 24 November 2010 13:41
		Re: Query taking 2 minutes By: eclipsewebmaster on Thu, 11 August 2011 14:23
		Re: Query taking 2 minutes By: naudefj on Mon, 17 October 2011 10:03

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) ] [

]

Current Time: Mon Feb 17 16:24:29 GMT 2025

Total time taken to generate the page: 0.06173 seconds