Details
-
Bug
-
Status: Closed (View Workflow)
-
Resolution: Duplicate
-
3.0.0
-
None
-
None
-
PHP Environment:
Database:
Description
This was an idea we discussed extensively in IRC and I tested it on STG with success, so I’m posting it here for reference.
The problem right now is that the query in the page_header(); function grabs the forum_id of the forum the user is browsing (if any), however, this is done using a LIKE where clause on a column with no index, for very large boards, this produces high load because of the frequency of this query.
The solution, I believe, is to add a session_forum_id column (with an index) to the sessions table, and specify the forum_id based on that. This limits the number of rows returned to just the rows needed and is not such a heavy burden on the Database to find those rows that are matching.
The following are the patch files for this change (aside from the DB change)
functions.php:
Index: includes/functions.php
|
===================================================================
|
--- includes/functions.php (revision 8423)
|
+++ includes/functions.php (working copy)
|
@@ -3148,7 +3148,7 @@
|
{
|
$f = request_var('f', 0);
|
|
- $reading_sql = ' AND s.session_page ' . $db->sql_like_expression("{$db->any_char}_f_={$f}x{$db->any_char}");
|
+ $reading_sql = ' AND s.session_forum_id = ' . $f;
|
}
|
|
// Get number of online guests
|
Index: includes/session.php
|
===================================================================
|
--- includes/session.php (revision 8423)
|
+++ includes/session.php (working copy)
|
@@ -185,7 +185,7 @@
|
}
|
|
// Add forum to the page for tracking online users - also adding a "x" to the end to properly identify the number
|
- $this->page['page'] .= (isset($_REQUEST['f'])) ? ((strpos($this->page['page'], '?') !== false) ? '&' : '?') . '_f_=' . (int) $_REQUEST['f'] . 'x' : '';
|
+ $this->page['forum_id'] = (isset($_REQUEST['f'])) ? intval($_REQUEST['f']) : 0;
|
|
if (isset($_COOKIE[$config['cookie_name'] . '_sid']) || isset($_COOKIE[$config['cookie_name'] . '_u']))
|
{
|
@@ -310,6 +310,7 @@
|
if ($this->update_session_page)
|
{
|
$sql_ary['session_page'] = substr($this->page['page'], 0, 199);
|
+ $sql_ary['session_forum_id'] = $this->page['forum_id'];
|
}
|
|
$sql = 'UPDATE ' . SESSIONS_TABLE . ' SET ' . $db->sql_build_array('UPDATE', $sql_ary) . "
|
@@ -550,6 +551,7 @@
|
if ($this->update_session_page)
|
{
|
$sql_ary['session_page'] = substr($this->page['page'], 0, 199);
|
+ $sql_ary['session_forum_id'] = $this->page['forum_id'];
|
}
|
|
$sql = 'UPDATE ' . SESSIONS_TABLE . ' SET ' . $db->sql_build_array('UPDATE', $sql_ary) . "
|
@@ -594,6 +596,7 @@
|
if ($this->update_session_page)
|
{
|
$sql_ary['session_page'] = (string) substr($this->page['page'], 0, 199);
|
+ $sql_ary['session_forum_id'] = $this->page['forum_id'];
|
}
|
|
$db->sql_return_on_error(true);
|
@@ -627,6 +630,7 @@
|
|
$sql_ary['session_id'] = (string) $this->session_id;
|
$sql_ary['session_page'] = (string) substr($this->page['page'], 0, 199);
|
+ $sql_ary['session_forum_id'] = $this->page['forum_id'];
|
|
$sql = 'INSERT INTO ' . SESSIONS_TABLE . ' ' . $db->sql_build_array('INSERT', $sql_ary);
|
$db->sql_query($sql);
|
Index: ../viewonline.php
|
===================================================================
|
--- viewonline.php (revision 8423)
|
+++ viewonline.php (working copy)
|
@@ -122,7 +122,7 @@
|
}
|
|
// Get user list
|
-$sql = 'SELECT u.user_id, u.username, u.username_clean, u.user_type, u.user_colour, s.session_id, s.session_time, s.session_page, s.session_ip, s.session_browser, s.session_viewonline
|
+$sql = 'SELECT u.user_id, u.username, u.username_clean, u.user_type, u.user_colour, s.session_id, s.session_time, s.session_page, s.session_forum_id, s.session_ip, s.session_browser, s.session_viewonline
|
FROM ' . USERS_TABLE . ' u, ' . SESSIONS_TABLE . ' s
|
WHERE u.user_id = s.session_user_id
|
AND s.session_time >= ' . (time() - ($config['load_online_time'] * 60)) .
|
@@ -139,7 +139,7 @@
|
{
|
$view_online = $s_user_hidden = false;
|
$user_colour = ($row['user_colour']) ? ' style="color:#' . $row['user_colour'] . '" class="username-coloured"' : '';
|
-
|
+
|
$username_full = ($row['user_type'] != USER_IGNORE) ? get_username_string('full', $row['user_id'], $row['username'], $row['user_colour']) : '<span' . $user_colour . '>' . $row['username'] . '</span>';
|
|
if (!$row['session_viewonline'])
|
@@ -208,8 +208,7 @@
|
case 'posting':
|
case 'viewforum':
|
case 'viewtopic':
|
- preg_match('#_f_=([0-9]+)x#i', $row['session_page'], $forum_id);
|
- $forum_id = (sizeof($forum_id)) ? (int) $forum_id[1] : 0;
|
+ $forum_id = $row['session_forum_id'];
|
|
if ($forum_id && $auth->acl_get('f_list', $forum_id))
|
{
|
@@ -334,7 +333,7 @@
|
'U_USER_IP' => append_sid("{$phpbb_root_path}viewonline.$phpEx", 'mode=lookup' . (($mode != 'lookup' || $row['session_id'] != $session_id) ? '&s=' . $row['session_id'] : '') . "&sg=$show_guests&start=$start&sk=$sort_key&sd=$sort_dir"),
|
'U_WHOIS' => append_sid("{$phpbb_root_path}viewonline.$phpEx", 'mode=whois&s=' . $row['session_id']),
|
'U_FORUM_LOCATION' => $location_url,
|
-
|
+
|
'S_USER_HIDDEN' => $s_user_hidden,
|
'S_GUEST' => ($row['user_id'] == ANONYMOUS) ? true : false,
|
'S_USER_TYPE' => $row['user_type'],
|
