Details
-
Bug
-
Status: Closed (View Workflow)
-
Resolution: Fixed
-
3.0.0
-
None
-
PHP Environment:
Database:
Description
In the functions.php page_header(); function, the display online list query can cause higher load than needed.
The SESSIONS_TABLE/USERS_TABLE joined query pulls all of the records in the SESSIONS table and then PHP runs through and filters them, but they could be filtered through SQL.
There is already a query to grab the guests browsing a specific forum or the entire board, but it is only used if $config['load_online_guests'] is set to true, yet the information retrieved is no different than the inner joined query.
I provided a patch below to (hopefully) illustrate my thoughts on this portion of code.
The difference should mean improved performance especially for large boards as it would pull significantly less rows on the joined query, yet returns the same data.
Index: functions.php
|
===================================================================
|
--- functions.php (revision 8423)
|
+++ functions.php (working copy)
|
@@ -3152,78 +3152,61 @@
|
}
|
|
// Get number of online guests
|
- if (!$config['load_online_guests'])
|
+ if ($db->sql_layer === 'sqlite')
|
{
|
- if ($db->sql_layer === 'sqlite')
|
- {
|
- $sql = 'SELECT COUNT(session_ip) as num_guests
|
- FROM (
|
- SELECT DISTINCT s.session_ip
|
- FROM ' . SESSIONS_TABLE . ' s
|
- WHERE s.session_user_id = ' . ANONYMOUS . '
|
- AND s.session_time >= ' . (time() - ($config['load_online_time'] * 60)) .
|
- $reading_sql .
|
- ')';
|
- }
|
- else
|
- {
|
- $sql = 'SELECT COUNT(DISTINCT s.session_ip) as num_guests
|
- FROM ' . SESSIONS_TABLE . ' s
|
- WHERE s.session_user_id = ' . ANONYMOUS . '
|
- AND s.session_time >= ' . (time() - ($config['load_online_time'] * 60)) .
|
- $reading_sql;
|
- }
|
- $result = $db->sql_query($sql);
|
- $guests_online = (int) $db->sql_fetchfield('num_guests');
|
- $db->sql_freeresult($result);
|
+ $sql = 'SELECT COUNT(session_ip) as num_guests
|
+ FROM (
|
+ SELECT DISTINCT s.session_ip
|
+ FROM ' . SESSIONS_TABLE . ' s
|
+ WHERE s.session_user_id = ' . ANONYMOUS . '
|
+ AND s.session_time >= ' . (time() - ($config['load_online_time'] * 60)) .
|
+ $reading_sql .
|
+ ')';
|
}
|
+ else
|
+ {
|
+ $sql = 'SELECT COUNT(DISTINCT s.session_ip) as num_guests
|
+ FROM ' . SESSIONS_TABLE . ' s
|
+ WHERE s.session_user_id = ' . ANONYMOUS . '
|
+ AND s.session_time >= ' . (time() - ($config['load_online_time'] * 60)) .
|
+ $reading_sql;
|
+ }
|
+ $result = $db->sql_query($sql);
|
+ $guests_online = (int) $db->sql_fetchfield('num_guests');
|
+ $db->sql_freeresult($result);
|
|
- $sql = 'SELECT u.username, u.username_clean, u.user_id, u.user_type, u.user_allow_viewonline, u.user_colour, s.session_ip, s.session_viewonline
|
+ $sql = 'SELECT u.username, u.username_clean, u.user_id, u.user_type, u.user_allow_viewonline, u.user_colour, s.session_viewonline
|
FROM ' . USERS_TABLE . ' u, ' . SESSIONS_TABLE . ' s
|
WHERE s.session_time >= ' . (time() - (intval($config['load_online_time']) * 60)) .
|
$reading_sql .
|
- ((!$config['load_online_guests']) ? ' AND s.session_user_id <> ' . ANONYMOUS : '') . '
|
+ ' AND s.session_user_id <> ' . ANONYMOUS . '
|
AND u.user_id = s.session_user_id
|
- ORDER BY u.username_clean ASC, s.session_ip ASC';
|
+ ORDER BY u.username_clean ASC';
|
$result = $db->sql_query($sql);
|
|
while ($row = $db->sql_fetchrow($result))
|
{
|
- // User is logged in and therefore not a guest
|
- if ($row['user_id'] != ANONYMOUS)
|
+ // Skip multiple sessions for one user
|
+ if ($row['user_id'] != $prev_user_id)
|
{
|
- // Skip multiple sessions for one user
|
- if ($row['user_id'] != $prev_user_id)
|
+ if ($row['session_viewonline'])
|
{
|
- if ($row['session_viewonline'])
|
- {
|
- $logged_visible_online++;
|
- }
|
- else
|
- {
|
- $row['username'] = '<em>' . $row['username'] . '</em>';
|
- $logged_hidden_online++;
|
- }
|
-
|
- if (($row['session_viewonline']) || $auth->acl_get('u_viewonline'))
|
- {
|
- $user_online_link = get_username_string(($row['user_type'] <> USER_IGNORE) ? 'full' : 'no_profile', $row['user_id'], $row['username'], $row['user_colour']);
|
- $online_userlist .= ($online_userlist != '') ? ', ' . $user_online_link : $user_online_link;
|
- }
|
+ $logged_visible_online++;
|
}
|
+ else
|
+ {
|
+ $row['username'] = '<em>' . $row['username'] . '</em>';
|
+ $logged_hidden_online++;
|
+ }
|
|
- $prev_user_id = $row['user_id'];
|
- }
|
- else
|
- {
|
- // Skip multiple sessions for one user
|
- if ($row['session_ip'] != $prev_session_ip)
|
+ if (($row['session_viewonline']) || $auth->acl_get('u_viewonline'))
|
{
|
- $guests_online++;
|
+ $user_online_link = get_username_string(($row['user_type'] <> USER_IGNORE) ? 'full' : 'no_profile', $row['user_id'], $row['username'], $row['user_colour']);
|
+ $online_userlist .= ($online_userlist != '') ? ', ' . $user_online_link : $user_online_link;
|
}
|
}
|
|
- $prev_session_ip = $row['session_ip'];
|
+ $prev_user_id = $row['user_id'];
|
}
|
$db->sql_freeresult($result);
|
|