| Query taking 2 minutes [message #161905] |
Fri, 19 February 2010 10:33  |
P_G_
Messages: 9
Registered: February 2010
Karma: 0
|
Junior Member |
add to buddy list
ignore all messages by this user
|
|
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;
|
|
|
|
|
|
| Re: Query taking 2 minutes [message #161907 is a reply to message #161906] |
Fri, 19 February 2010 15:09  |
P_G_
Messages: 9
Registered: February 2010
Karma: 0
|
Junior Member |
add to buddy list
ignore all messages by this user
|
|
Certainly can, it is:
+----+-------------+-------+--------+-------------------------------------- ---------+---------------------------+---------+--------------------------- --+-------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+-------------------------------------- ---------+---------------------------+---------+--------------------------- --+-------+---------------------------------+
| 1 | SIMPLE | c | system | PRIMARY | NULL | NULL | NULL | 1 | Using temporary; Using filesort |
| 1 | SIMPLE | l | system | PRIMARY | NULL | NULL | NULL | 0 | const row not found |
| 1 | SIMPLE | f | const | PRIMARY,fud26_forum_i_c | PRIMARY | 4 | const | 1 | |
| 1 | SIMPLE | g1 | const | fud26_group_cache_i_ru,fud26_group_cache_i_u | fud26_group_cache_i_ru | 8 | const,const | 1 | |
| 1 | SIMPLE | g2 | const | fud26_group_cache_i_ru,fud26_group_cache_i_u | fud26_group_cache_i_ru | 8 | const,const | 0 | unique row not found |
| 1 | SIMPLE | t | ref | PRIMARY,fud26_thread_i_flm,fud26_thread_i_rmi | fud26_thread_i_flm | 4 | const | 15724 | |
| 1 | SIMPLE | r | eq_ref | fud26_read_i_tu,fud26_read_i_u | fud26_read_i_tu | 8 | cardiffonline.t.id,const | 1 | |
| 1 | SIMPLE | m | ref | fud26_msg_i_ta,fud26_msg_i_ps,fud26_msg_i_a | fud26_msg_i_ta | 8 | cardiffonline.t.id,const | 9 | Using where |
| 1 | SIMPLE | u | eq_ref | PRIMARY | PRIMARY | 4 | cardiffonline.m.poster_id | 1 | |
| 1 | SIMPLE | p | eq_ref | PRIMARY | PRIMARY | 4 | cardiffonline.m.poll_id | 1 | |
| 1 | SIMPLE | pot | eq_ref | fud26_poll_opt_track_i_pu | fud26_poll_opt_track_i_pu | 8 | cardiffonline.p.id,const | 1 | |
| 1 | SIMPLE | mm | const | fud26_mod_i_uf | fud26_mod_i_uf | 8 | const,const | 1 | Using where |
| 1 | SIMPLE | m2 | eq_ref | PRIMARY | PRIMARY | 4 | cardiffonline.t.root_msg_id | 1 | |
+----+-------------+-------+--------+-------------------------------------- ---------+---------------------------+---------+--------------------------- --+-------+---------------------------------+
|
|
|
|
|
|
|
|
|
|
| Re: Query taking 2 minutes [message #161936 is a reply to message #161920] |
Mon, 22 February 2010 05:57 |
P_G_
Messages: 9
Registered: February 2010
Karma: 0
|
Junior Member |
add to buddy list
ignore all messages by this user
|
|
Okay, after having a play around with a few things, I have the query down to between 10 and 18 seconds (from between 90 and 120), ending up with doing the following:
- ALTER TABLE fud26_thread ADD INDEX fud26_forum_thread (forum_id, id), and;
- adding "FORCE INDEX (fud26_forum_thread)" AFTER "INNER JOIN fud26_thread t" on line 815 of fudforum/theme/default/selmsg.php.
It's obviously a lot better, but still not perfect, so hopefully it is something you can improve on further?
I also assume that this is something called upon while in the admin panel only. What task actually runs this statement, and are we perhaps able to avoid it at all?
Cheers
P_G_
|
|
|
|
|
|
| Re: Query taking 2 minutes [message #161941 is a reply to message #161940] |
Mon, 22 February 2010 12:18 |
P_G_
Messages: 9
Registered: February 2010
Karma: 0
|
Junior Member |
add to buddy list
ignore all messages by this user
|
|
Thanks again for replying, but I'm not quite sure what you are suggesting, or if you are suggesting anything, to be honest.
You know your software better than me  , and I'm afraid I can't find where a 'Message Navigator' is, or how it is run. Trying to run the URL of the page I altered the SQL on doesn't work, so clearly it has a parent file it runs within.
I only noticed the query initially from the 'show processlist' command, and indeed subsequently tested these additional indexes from the MySQL command prompt.
I suppose ideally it's whether you can optimise further, or, if not, whether the command be altered or removed, either via your coding or an option in the admin panel?
Thanks
[Updated on: Mon, 22 February 2010 12:19] Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Calendar
Search
Help
Members
Control Panel
Logout [ CommonCrawl [Bot] ]
Home
]