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
Switch to threaded view of this topic Create a new topic Submit Reply
Query taking 2 minutes [message #161905] Fri, 19 February 2010 15:33 Go to next message
P_G_ is currently offline  P_G_
Messages: 9
Registered: February 2010
Karma: 0
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;
Re: Query taking 2 minutes [message #161906 is a reply to message #161905] Fri, 19 February 2010 19:58 Go to previous messageGo to next message
naudefj is currently offline  naudefj   South Africa
Messages: 3771
Registered: December 2004
Karma: 28
Senior Member
Administrator
Core Developer
Can you please post an EXPLAIN for the statement?

PS: This query looks all too familiar. I've tried to "tune" it before, but was unable to get it any faster.
Re: Query taking 2 minutes [message #161907 is a reply to message #161906] Fri, 19 February 2010 20:09 Go to previous messageGo to next message
P_G_ is currently offline  P_G_
Messages: 9
Registered: February 2010
Karma: 0
Junior Member
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 #161908 is a reply to message #161907] Fri, 19 February 2010 20:54 Go to previous messageGo to next message
naudefj is currently offline  naudefj   South Africa
Messages: 3771
Registered: December 2004
Karma: 28
Senior Member
Administrator
Core Developer
OK, the slow-down is probably caused by fetching 15724 rows from fud26_thread. MySQL uses the thread_i_flm index (which is probably not selective enough).

Looking at the query, candidate keys would be:

* id
* root_msg_id
* forum_id
* last_post_date (in ORDER BY)

Existing indexes:

* PRIMARY (id)
* thread_i_flm (forum_id, moved_to) <-- use for leading col.
* thread_i_t (thread_opt)
* thread_i_rmi (root_msg_id)
* thread_i_r (replies)

Will you be able to create some indexes to see it they will help?
Re: Query taking 2 minutes [message #161910 is a reply to message #161908] Sat, 20 February 2010 12:54 Go to previous messageGo to next message
P_G_ is currently offline  P_G_
Messages: 9
Registered: February 2010
Karma: 0
Junior Member
I am able to create indexes in the database, and even modify the code if need be (maybe to force use of an index?), so if you do have any suggestions as to what to add or alter, it would be very much appreciated.

Thanks

[Updated on: Sat, 20 February 2010 12:54]

Report message to a moderator

Re: Query taking 2 minutes [message #161920 is a reply to message #161910] Sun, 21 February 2010 00:08 Go to previous messageGo to next message
naudefj is currently offline  naudefj   South Africa
Messages: 3771
Registered: December 2004
Karma: 28
Senior Member
Administrator
Core Developer
One would have to play with various combinations (trial and error). Some indexes that may be worth trying:

(forum_id, root_msg_id)
(forum_id, id)
(id, forum_id, root_msg_id)
(last_post_date)
(last_post_date, forum_id)
Etc.
Re: Query taking 2 minutes [message #161936 is a reply to message #161920] Mon, 22 February 2010 10:57 Go to previous messageGo to next message
P_G_ is currently offline  P_G_
Messages: 9
Registered: February 2010
Karma: 0
Junior Member
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 #161940 is a reply to message #161936] Mon, 22 February 2010 16:06 Go to previous messageGo to next message
naudefj is currently offline  naudefj   South Africa
Messages: 3771
Registered: December 2004
Karma: 28
Senior Member
Administrator
Core Developer
P_G_ wrote:
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:


Good progress. I just wonder why MySQL cannot pick the right indexes by itself.

P_G_ wrote:
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?


Looks like the Message Navigator. However, I've never waited that long for a FUDforum screen to return, so I might be a background caching operation.
Re: Query taking 2 minutes [message #161941 is a reply to message #161940] Mon, 22 February 2010 17:18 Go to previous messageGo to next message
P_G_ is currently offline  P_G_
Messages: 9
Registered: February 2010
Karma: 0
Junior Member
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 Razz, 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 17:19]

Report message to a moderator

Re: Query taking 2 minutes [message #161949 is a reply to message #161941] Tue, 23 February 2010 19:19 Go to previous messageGo to next message
naudefj is currently offline  naudefj   South Africa
Messages: 3771
Registered: December 2004
Karma: 28
Senior Member
Administrator
Core Developer
I'm not making any suggestions. Unfortunately I don't know enough about MySQL to know what the best solution would be. I was actually hoping someone would jump in to help us.

PS: The Message Navigator for this forum is at http://fudforum.org/forum/index.php?t=mnav&
Re: Query taking 2 minutes [message #163684 is a reply to message #161949] Wed, 24 November 2010 10:34 Go to previous messageGo to next message
Ernesto is currently offline  Ernesto   Sweden
Messages: 413
Registered: August 2005
Karma: 0
Senior Member
I've been trying to wrestle with this when trying to adjust the selmsg (Today's Posts / Unread posts / Message Navigator) to dislay only topics with new messages instead of all the messages.

The problem occurs when trying to "order by" and I cannot for the life of me tune it to perform better.

For example if you register a new user, read a few old posts and then hit the unread messages button found in the SHOW template, it will take a VERY long time to finish up.

I tried removing the ORDER BY and use a GROUP BY instead, and then limit it, but it doesn't work well since the ordering comes out bananas, showing several year old messages etc.

I was thinking that perhaps one could insert a timelimit, but that wont help on a busy forum with hundreds of posts per day etc.

I haven't given up completely, but until we solve this, I think the selmsg page should be disabled on new releases, it is rather easy to kill a server by doing a few calls to the message navigator or "unread" messages in selmsg.


Re: Query taking 2 minutes [message #163685 is a reply to message #163684] Wed, 24 November 2010 11:23 Go to previous messageGo to next message
Ernesto is currently offline  Ernesto   Sweden
Messages: 413
Registered: August 2005
Karma: 0
Senior Member
I stripped off the ORDER BY clause in the SQL and added a group by t.id. I also changed the default sort order to DESC instead of ASC

The final echoed SQL for the "Unread posts" option now looks like this, when echoed:
SELECT 
m.id, m.thread_id, m.poster_id, m.post_stamp, m.msg_opt, m.apr,
t.id AS topic_id, t.thread_opt, t.root_msg_id, t.last_post_date, t.last_post_id, t.forum_id, t.views, t.replies, t.tdescr, 
f.message_threshold, f.name, 
u.id AS user_id, u.alias AS login, 
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=2002 AND g2.resource_id=f.id 
LEFT JOIN fud26_read r ON r.thread_id=t.id AND r.user_id=2002 
LEFT JOIN fud26_users u ON m.poster_id=u.id 
LEFT JOIN fud26_mod mm ON mm.forum_id=f.id AND mm.user_id=2002 
WHERE m.apr=1 
AND m.post_stamp > 0
AND (mm.id IS NOT NULL OR (COALESCE(g2.group_cache_opt, g1.group_cache_opt) & 2) > 0) 
GROUP BY t.id DESC LIMIT 0,40


This is however, not a suitable change if you wanna keep the functionality of showing all messages.

Basically what I changed was that I removed all ORDER BY, grouped it by thread_id (t.id) and stripped away unecessary columns from the message table that are not needed when displaying only topics.

I am not done yet, but obviously the ordering is the criminal here, especially when it tries to order hundreds of thousands of rows and entries.

I will try to post my alterations when done, still tweaking things for speed.


Re: Query taking 2 minutes [message #163686 is a reply to message #163685] Wed, 24 November 2010 11:25 Go to previous messageGo to next message
Ernesto is currently offline  Ernesto   Sweden
Messages: 413
Registered: August 2005
Karma: 0
Senior Member
My change into displaying just topics instead of messages also means I am removing any and all auto "mark as read" functionality.

Perhaps my changes are better suited in a new page for those that wanna keep the display all new messages functionality, but I honestly doubt it - this is a forum with threaded/tree views of topics and discussions, to me it's madness not to show previous entries in a thread but display just the new messages - it sort of defeats the purpose of a forum - Maybe it's a functionality that make sense together with maillist integration though.


Re: Query taking 2 minutes [message #163687 is a reply to message #163686] Wed, 24 November 2010 12:01 Go to previous messageGo to next message
Ernesto is currently offline  Ernesto   Sweden
Messages: 413
Registered: August 2005
Karma: 0
Senior Member
Alright, this is my "final" selmsg.php.t

The sorting is obviously not perfect, it orders by thread ID found in the thread table, so the highest thread ID is at the top. However, the SQL takes 0.0x seconds instead of XX.xx seconds, which is an improvement with several thousand percent Razz

The "more unread messages" functionality is not working since the $usr->data variable (object?) contains nothing since I no longer call tmpl_drawmsg function.

This will have to be solved some other way, should not be hard, will work on that later.

The pager is also not working properly, it will have to be adjusted, not worried right now though.

<?php
/**
* copyright            : (C) 2001-2007 Advanced Internet Designs Inc.
* email                : forum(at)prohost(dot)org
* $Id: selmsg.php.t,v 1.75 2007/01/01 18:23:46 hackie Exp $
*
* This program is free software; you can redistribute it and/or modify it
* under the terms of the GNU General Public License as published by the
* Free Software Foundation; version 2 of the License.
**/

/*{PRE_HTML_PHP}*/

function path_info_lnk($var, $val)
{
	$a = $_GET;
	unset($a['rid'], $a['S'], $a['t']);
	if (isset($a[$var])) {
		unset($a[$var]);
		$rm = 1;
	}
	$url = '/sel';

	foreach ($a as $k => $v) {
		$url .= '/' . $k . '/' . $v;
	}
	if (!isset($rm)) {
		$url .= '/' . $var . '/' . $val;
	}

	return htmlspecialchars($url, ENT_QUOTES) . '/' . _rsid;
}



	ses_update_status($usr->sid, '{TEMPLATE: selmsg_update}');

	$count = $usr->posts_ppg ? $usr->posts_ppg : $POSTS_PER_PAGE;
	if (!isset($_GET['start']) || !($start = (int)$_GET['start'])) {
		$start = 0;
	}

	/* limited to today */
	if (isset($_GET['date'])) {
		if ($_GET['date'] != 'today') {
			$tm = __request_timestamp__ - ((int)$_GET['date'] - 1) * 86400;
		} else {
			$tm = __request_timestamp__;
		}
		$dt = getdate($tm);
		$tm_today_start = mktime(0, 0, 0, $dt['mon'], $dt['mday'], $dt['year']);
		$tm_today_end = $tm_today_start + 86400;
		$date_limit = ' AND m.post_stamp>'.$tm_today_start.' AND m.post_stamp<'.$tm_today_end . ' ';
	} else {
		$date_limit = '';
	}
	if (!_uid) { /* these options are restricted to registered users */
		unset($_GET['sub_forum_limit'], $_GET['sub_th_limit'], $_GET['unread']);
	}

	$unread_limit = (isset($_GET['unread']) && _uid) ? ' AND m.post_stamp > '.$usr->last_read.' AND (r.id IS NULL OR r.last_view < m.post_stamp) ' : '';
	$th = isset($_GET['th']) ? (int)$_GET['th'] : 0;
	$frm_id = isset($_GET['frm_id']) ? (int)$_GET['frm_id'] : 0;
	$perm_limit = $is_a ? '' : ' AND (mm.id IS NOT NULL OR ' . (_uid ? '(COALESCE(g2.group_cache_opt, g1.group_cache_opt)' : '(g1.group_cache_opt') . ' & 2) > 0)';

	/* mark messages read for registered users */

	if (_uid && isset($_GET['mr']) && !empty($usr->data)) {
		foreach ($usr->data as $ti => $mi) {
			if (!(int)$ti || !(int)$mi) {
				break;
			}
			user_register_thread_view($ti, __request_timestamp__, $mi);
		}
	}
	ses_putvar((int)$usr->sid, null);

	/* no other limiters are present, assume 'today' limit */
	if (!$unread_limit && !isset($_GET['date']) && !isset($_GET['reply_count'])) {
		$_GET['date'] = 'today';
		$dt = getdate(__request_timestamp__);
		$tm_today_start =  mktime(0, 0, 0, $dt['mon'], $dt['mday'], $dt['year']);
		$tm_today_end = $tm_today_start + 86400;
		$date_limit = ' AND m.post_stamp>'.$tm_today_start.' AND m.post_stamp<'.$tm_today_end . ' ';
	}

	$_SERVER['QUERY_STRING'] = htmlspecialchars($_SERVER['QUERY_STRING'], ENT_QUOTES);

	/* date limit */
	if ($FUD_OPT_2 & 32768) {
		$dt_opt = path_info_lnk('date', '1');
		$rp_opt = path_info_lnk('reply_count', '0');
	} else {
		$dt_opt = isset($_GET['date']) ? str_replace('&amp;date='.$_GET['date'], '', $_SERVER['QUERY_STRING']) : $_SERVER['QUERY_STRING'] . '&amp;date=1';
		$rp_opt = isset($_GET['reply_count']) ? str_replace('&amp;reply_count='.(int)$_GET['reply_count'], '', $_SERVER['QUERY_STRING']) : $_SERVER['QUERY_STRING'] . '&amp;reply_count=0';
	}

	if (_uid) {
		if ($FUD_OPT_2 & 32768) {
			$un_opt = path_info_lnk('unread', '1');
			$frm_opt = path_info_lnk('sub_forum_limit', '1');
			$th_opt =path_info_lnk('sub_th_limit', '1');
		} else {
			$un_opt = isset($_GET['unread']) ? str_replace('&amp;unread='.$_GET['unread'], '', $_SERVER['QUERY_STRING']) : $_SERVER['QUERY_STRING'] . '&amp;unread=1';
			$frm_opt = isset($_GET['sub_forum_limit']) ? str_replace('&amp;sub_forum_limit='.$_GET['sub_forum_limit'], '', $_SERVER['QUERY_STRING']) : $_SERVER['QUERY_STRING'] . '&amp;sub_forum_limit=1';
			$th_opt = isset($_GET['sub_th_limit']) ? str_replace('&amp;sub_th_limit='.$_GET['sub_th_limit'], '', $_SERVER['QUERY_STRING']) : $_SERVER['QUERY_STRING'] . '&amp;sub_th_limit=1';
		}
	}

	make_perms_query($fields, $join);

	if (!$unread_limit) {
		$total = (int) q_singleval('SELECT count(*) FROM {SQL_TABLE_PREFIX}msg m INNER JOIN {SQL_TABLE_PREFIX}thread t ON m.thread_id=t.id INNER JOIN {SQL_TABLE_PREFIX}forum f ON t.forum_id=f.id INNER JOIN {SQL_TABLE_PREFIX}cat c ON f.cat_id=c.id '.(isset($_GET['sub_forum_limit']) ? 'INNER JOIN {SQL_TABLE_PREFIX}forum_notify fn ON fn.forum_id=f.id AND fn.user_id='._uid : '').' '.(isset($_GET['sub_th_limit']) ? 'INNER JOIN {SQL_TABLE_PREFIX}thread_notify tn ON tn.thread_id=t.id AND tn.user_id='._uid : '').' '.$join.' LEFT JOIN {SQL_TABLE_PREFIX}mod mm ON mm.forum_id=f.id AND mm.user_id='._uid.' WHERE m.apr=1 '.$date_limit.' '.($frm_id ? ' AND f.id='.$frm_id : '').' '.($th ? ' AND t.id='.$th : '').' '.(isset($_GET['reply_count']) ? ' AND t.replies='.(int)$_GET['reply_count'] : '').' '.$perm_limit);
	}

/*{POST_HTML_PHP}*/

/* Inverted sort order, newest topic first */

	if ($unread_limit || $total) {
		$ord = isset($_GET['reply_count']) ? ' ASC ' : ' DESC ';

		/* figure out the query */
/*
		$c = q('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, t.tdescr,
			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,
			'.$fields.'
		FROM
			{SQL_TABLE_PREFIX}msg m
			INNER JOIN {SQL_TABLE_PREFIX}thread t ON m.thread_id=t.id
			INNER JOIN {SQL_TABLE_PREFIX}msg m2 ON m2.id=t.root_msg_id
			INNER JOIN {SQL_TABLE_PREFIX}forum f ON t.forum_id=f.id
			INNER JOIN {SQL_TABLE_PREFIX}cat c ON f.cat_id=c.id
			'.(isset($_GET['sub_forum_limit']) ? 'INNER JOIN {SQL_TABLE_PREFIX}forum_notify fn ON fn.forum_id=f.id AND fn.user_id='._uid : '').'
			'.(isset($_GET['sub_th_limit']) ? 'INNER JOIN {SQL_TABLE_PREFIX}thread_notify tn ON tn.thread_id=t.id AND tn.user_id='._uid : '').'
			'.$join.'
			LEFT JOIN {SQL_TABLE_PREFIX}read r ON r.thread_id=t.id AND r.user_id='._uid.'
			LEFT JOIN {SQL_TABLE_PREFIX}users u ON m.poster_id=u.id
			LEFT JOIN {SQL_TABLE_PREFIX}level l ON u.level_id=l.id
			LEFT JOIN {SQL_TABLE_PREFIX}poll p ON m.poll_id=p.id
			LEFT JOIN {SQL_TABLE_PREFIX}poll_opt_track pot ON pot.poll_id=p.id AND pot.user_id='._uid.'
			LEFT JOIN {SQL_TABLE_PREFIX}mod mm ON mm.forum_id=f.id AND mm.user_id='._uid.'
		WHERE
			m.apr=1
			'.$date_limit.'
			'.($frm_id ? ' AND f.id='.$frm_id : '').'
			'.($th ? ' AND t.id='.$th : '').'
			'.(isset($_GET['reply_count']) ? ' AND t.replies='.(int)$_GET['reply_count'] : '').'
			'.$unread_limit.'
			'.$perm_limit.'
		ORDER BY
			f.last_post_id '.$ord.', t.last_post_date '.$ord.', m.post_stamp '.$ord.'
		LIMIT '.qry_limit($count, $start));

*/

/* SQL moved into a variable to be able to echo the SQL */
$sql = 'SELECT
			m.id, m.thread_id, m.poster_id, m.post_stamp, m.msg_opt, m.apr,
			t.id AS topic_id, t.thread_opt, t.root_msg_id, t.last_post_date, t.last_post_id, t.forum_id, t.views, t.replies, t.tdescr, 
			f.message_threshold, f.name, 
			u.id AS user_id, u.alias AS login, 
			r.last_view,
			mm.id AS md,
			m2.subject AS thr_subject,
			'.$fields.'
		FROM
			{SQL_TABLE_PREFIX}msg m
			INNER JOIN {SQL_TABLE_PREFIX}thread t ON m.thread_id=t.id
			INNER JOIN {SQL_TABLE_PREFIX}msg m2 ON m2.id=t.root_msg_id
			INNER JOIN {SQL_TABLE_PREFIX}forum f ON t.forum_id=f.id
			INNER JOIN {SQL_TABLE_PREFIX}cat c ON f.cat_id=c.id
			'.(isset($_GET['sub_forum_limit']) ? 'INNER JOIN {SQL_TABLE_PREFIX}forum_notify fn ON fn.forum_id=f.id AND fn.user_id='._uid : '').'
			'.(isset($_GET['sub_th_limit']) ? 'INNER JOIN {SQL_TABLE_PREFIX}thread_notify tn ON tn.thread_id=t.id AND tn.user_id='._uid : '').'
			'.$join.'
			LEFT JOIN {SQL_TABLE_PREFIX}read r ON r.thread_id=t.id AND r.user_id='._uid.'
			LEFT JOIN {SQL_TABLE_PREFIX}users u ON m.poster_id=u.id
			LEFT JOIN {SQL_TABLE_PREFIX}mod mm ON mm.forum_id=f.id AND mm.user_id='._uid.'
		WHERE
			m.apr=1
			'.$date_limit.'
			'.($frm_id ? ' AND f.id='.$frm_id : '').'
			'.($th ? ' AND t.id='.$th : '').'
			'.(isset($_GET['reply_count']) ? ' AND t.replies='.(int)$_GET['reply_count'] : '').'
			'.$unread_limit.'
			'.$perm_limit.'
		GROUP BY
		t.id '.$ord.'
		LIMIT '.qry_limit($count, $start);


$c = q($sql);
unset($sql);

		/* message drawing code */
		$message_data = $n = $prev_frm = $prev_th = '';
		$thl = $mark_read = array();
		
		
		while ($r = db_rowobj($c)) {
			if ($prev_frm != $r->forum_id) {
				$prev_frm = $r->forum_id;
		//	$message_data .= '{TEMPLATE: forum_row}'; // I feel there is no need to output the forum on a separate row 
				$perms = perms_from_obj($r, $is_a);
			}
			if ($prev_th != $r->thread_id) {
				$thl[] = $r->thread_id;
				$prev_th = $r->thread_id;
				$message_data .= '{TEMPLATE: thread_row}';
			}
			/* Removing automatic mark as read */
			
			/*	if (_uid && $r->last_view < $r->post_stamp && $r->post_stamp > $usr->last_read && !isset($mark_read[$r->thread_id])) {
				$mark_read[$r->thread_id] = $r->id;
			}
			*/
			$usr->md = $r->md;

			/* We do not want to draw the messages anymore */
	//		$message_data .= '<tr><th colspan="3">'.$r->id.'</th></tr>';
	//		$message_data .= tmpl_drawmsg($r, $usr, $perms, false, $n, '');
		}
		unset($c);

/* Remove the extra views update - we are not viewing the threads/messages */

/*
		if ($thl) {
			q('UPDATE {SQL_TABLE_PREFIX}thread SET views=views+1 WHERE id IN('.implode(',', $thl).')');
		}
*/
		if (_uid && $mark_read) {
			ses_putvar((int)$usr->sid, $mark_read);
		}
		
	} else {
		$message_data = '';
	}

/* FIX - this isn't working - $usr->data is empty */

	if (_uid && isset($_GET['mr']) && !empty($message_ids)) {
		foreach ($usr->data as $ti => $mi) {
			if (!(int)$ti || !(int)$mi) {
				break;
			}
			user_register_thread_view($ti, __request_timestamp__, $mi);
		}
	}


		
	if (!$unread_limit && $total > $count) {
		

		if (!isset($_GET['mr'])) {
			if ($FUD_OPT_2 & 32768 && isset($_SERVER['PATH_INFO'])) {
				$_SERVER['PATH_INFO'] .= 'mr/1/';
			} else {
				$_SERVER['QUERY_STRING'] .= '&mr=1';
			}
		}

		
		/* Preserve pager */
		
		/* FIX FIX FIX Pager still needs work, it is counting messages, not topics */
		
		if ($FUD_OPT_2 & 32768 && isset($_SERVER['PATH_INFO'])) {
			$p = htmlspecialchars(str_replace(_rsid, '', $_SERVER['PATH_INFO']), ENT_QUOTES);
			if (strpos($p, 'start/') !== false) {
				$p = preg_replace('!start/[0-9]+/!', '', $p);
			}
			$pager = tmpl_create_pager($start, $count, $total, '{ROOT}' . $p . 'start/', '/' . _rsid);
		} else {
			$pager = tmpl_create_pager($start, $count, $total, '{ROOT}?' . str_replace('&amp;start='.$start, '', $_SERVER['QUERY_STRING']));
		}
	} else if ($unread_limit) {
		
		/* Fixed SEO GET string */
		if (!isset($_GET['mark_page_read'])) {
			if ($FUD_OPT_2 & 32768) {
				$_SERVER['QUERY_STRING'] = htmlspecialchars(str_replace(_rsid, '', $_SERVER['PATH_INFO']), ENT_QUOTES) . 'mark_page_read/1/mr/1/' . _rsid;
			} else {
				$_SERVER['QUERY_STRING'] .= '&amp;mark_page_read=1&amp;mr=1';
			}
		}
		
		$pager = $message_data ? '{TEMPLATE: more_unread_messages}' : '';
	} else {
		$pager = '';
	}

	if (!$message_data) {
		if (isset($_GET['unread'])) {
			
			$message_data = '{TEMPLATE: no_unread_messages}';
			/*
			if (!$frm_id && !$th) {
				user_mark_all_read(_uid);
			} else if ($frm_id) {
				user_mark_forum_read(_uid, $frm_id, $usr->last_read);
			}
		} else {
		*/
			$message_data = '{TEMPLATE: no_result}';
		}
	}

/*{POST_PAGE_PHP_CODE}*/
?>
{TEMPLATE: SELMSG_PAGE}


Re: Query taking 2 minutes [message #163688 is a reply to message #163687] Wed, 24 November 2010 12:04 Go to previous messageGo to next message
Ernesto is currently offline  Ernesto   Sweden
Messages: 413
Registered: August 2005
Karma: 0
Senior Member
I'll get to posting the template changes also, will be funtionality, not design since I made it to fit with my forum and not the default theme.

[Updated on: Wed, 24 November 2010 12:05]

Report message to a moderator

Re: Query taking 2 minutes [message #163689 is a reply to message #163688] Wed, 24 November 2010 12:21 Go to previous messageGo to next message
Ernesto is currently offline  Ernesto   Sweden
Messages: 413
Registered: August 2005
Karma: 0
Senior Member
selmsg.tmpl -> thread_row
<tr class="ThreadRow">
<td class="ThreadRowB"><img src="{THEME_IMAGE_ROOT}/unread.png" alt="[ ]"></td>
<td class="ThreadRowB">
	<a href="{TEMPLATE: thread_row_lnk}" title="Go to last unread message">
		<img src="{THEME_IMAGE_ROOT}/newposts.gif" alt="->">
	</a> 
	<h3>
		<a class="big" href="{ROOT}/t/{VAR: r->thread_id}/">
			{VAR: r->thr_subject}
		</a>
	</h3>
	{IF: $r->tdescr}
		<br>
			<span class="small" style="color: #999999">{VAR: r->tdescr}</span>
	{ENDIF}
	<br>
	{MSG: last_post} {TEMPLATE: selmsg_last_poster_link} {TEMPLATE: selmsg_last_post_date} 
</td>
<td class="ThreadRowB">
	<ul title="" class="threadstats SmallText">
		<li>{MSG: replies}: {VAR: r->replies}</li>
		<li>{MSG: views}: {VAR: r->views}</li>
	</ul>
</td>
<td class="ThreadRowB" style="border-right: 1px solid #CCC;">
	{MSG: forum}<br>
	<a href="{TEMPLATE: forum_row_lnk}">
		{VAR: r->name}
	</a>
</td>
</tr>




New template sections inside selmsg.tmpl
{SECTION: selmsg_last_poster_link}
<a href="{TEMPLATE: selmsg_last_poster_lnk}">{VAR: r->login}</a>
{SECTION: END}
{SECTION: selmsg_last_poster_lnk}
{ROOT}/u/{VAR: r->user_id}/{VAR: r->login}/
{SECTION: END}
{SECTION: selmsg_last_post_date}
{DATE: r->post_stamp %a, %d %b %Y %H:%M}
{SECTION: END}

Non PATH_INFO version of the userlink you can create yourself! HAH!


[Updated on: Wed, 24 November 2010 12:28]

Report message to a moderator

Re: Query taking 2 minutes [message #163690 is a reply to message #163689] Wed, 24 November 2010 12:30 Go to previous messageGo to next message
Ernesto is currently offline  Ernesto   Sweden
Messages: 413
Registered: August 2005
Karma: 0
Senior Member
The mnav is working quickly, it is very strange that the SQL for that one should work so smooth, while the SQL for selmsg is grinding to a death halt

Re: Query taking 2 minutes [message #163691 is a reply to message #163689] Wed, 24 November 2010 12:47 Go to previous messageGo to next message
naudefj is currently offline  naudefj   South Africa
Messages: 3771
Registered: December 2004
Karma: 28
Senior Member
Administrator
Core Developer
Good heavens!
It's impossible to see what changes you've made.
Also, working on code from 2007 is not making it any easier.
Re: Query taking 2 minutes [message #163692 is a reply to message #163691] Wed, 24 November 2010 13:35 Go to previous messageGo to next message
Ernesto is currently offline  Ernesto   Sweden
Messages: 413
Registered: August 2005
Karma: 0
Senior Member
I am sorry =( - With syntax highlighting it should be better

I have changed the SQL and put comments at the places i have changed things.

I have commented out the original things instead of removing them to show it more clearly.


[Updated on: Wed, 24 November 2010 13:35]

Report message to a moderator

Re: Query taking 2 minutes [message #163693 is a reply to message #163692] Wed, 24 November 2010 13:41 Go to previous messageGo to next message
Ernesto is currently offline  Ernesto   Sweden
Messages: 413
Registered: August 2005
Karma: 0
Senior Member
The code should be accurate as of Apr 27 06:56:52 2010 btw, it's not like much stuff changed in that file over time hehe.

I usually keep my files more or less up to date if anything relevant to me have been updated that should be included, i spot patch things, etc.

My forum version should now be 2.david+frank=true instead of 2.7.7


Re: Query taking 2 minutes [message #165791 is a reply to message #161906] Thu, 11 August 2011 14:23 Go to previous messageGo to next message
eclipsewebmaster is currently offline  eclipsewebmaster   
Messages: 46
Registered: November 2009
Location: Ottawa, Ontario, Canada
Karma: 0
Member
naudefj wrote on Fri, 19 February 2010 14:58
PS: This query looks all too familiar. I've tried to "tune" it before, but was unable to get it any faster.


I've posted another thread about this.

http://fudforum.org/forum/index.php?t=msg&th=120085&unread=1&

This query is part of the "Unanswered Messages" features. I don't think you'll be able to optimize this query any further. To make matters worse, no matter how aggressively you tune MySQL's buffers, it will more than likely create a rather large tmp table on disk.
Re: Query taking 2 minutes [message #166254 is a reply to message #165791] Mon, 17 October 2011 10:03 Go to previous message
naudefj is currently offline  naudefj   South Africa
Messages: 3771
Registered: December 2004
Karma: 28
Senior Member
Administrator
Core Developer
Unanswered messages was optimized as per:
http://sourceforge.net/apps/trac/fudforum/changeset/5395
  Switch to threaded view of this topic Create a new topic Submit Reply
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 Mar 28 22:36:54 GMT 2024

Total time taken to generate the page: 0.02767 seconds