Uploaded image for project: 'phpBB'
  1. phpBB
  2. PHPBB-7063

Poor query format for display online list

XMLWordPrintable

    • Icon: Bug Bug
    • Resolution: Fixed
    • 3.0.1
    • 3.0.0
    • Sessions
    • None
    • PHP Environment:
      Database:

      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.

      http://phpfi.com/300804

      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);
       

            Kellanved Kellanved [X] (Inactive)
            Highway of Life David Lewis [X] (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

              Created:
              Updated:
              Resolved: