Assigned ticket to user "Kellanved"
Action performed by Acyd Burn (Server Manager) on Mar 13th 2008, 14:50
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.
http://phpfi.com/300804
- Code: Select all
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);
