diff options
| author | rxu <rxu@mail.ru> | 2019-11-03 23:14:52 +0700 |
|---|---|---|
| committer | rxu <rxu@mail.ru> | 2019-11-03 23:49:00 +0700 |
| commit | 46a68d37ee858db19b3cb9100ff7f4be9e5e994e (patch) | |
| tree | 55db860634dc5472d54f4e1464fbaff270382452 | |
| parent | e9fc3ed22e879c7a33bf5b77e4fa51e88815e612 (diff) | |
| download | forums-46a68d37ee858db19b3cb9100ff7f4be9e5e994e.tar forums-46a68d37ee858db19b3cb9100ff7f4be9e5e994e.tar.gz forums-46a68d37ee858db19b3cb9100ff7f4be9e5e994e.tar.bz2 forums-46a68d37ee858db19b3cb9100ff7f4be9e5e994e.tar.xz forums-46a68d37ee858db19b3cb9100ff7f4be9e5e994e.zip | |
[ticket/15294] Another approach, optimize updating & purging expired sessions
PHPBB3-15294
| -rw-r--r-- | phpBB/phpbb/session.php | 63 |
1 files changed, 31 insertions, 32 deletions
diff --git a/phpBB/phpbb/session.php b/phpBB/phpbb/session.php index 4552f3bd82..60551bfe99 100644 --- a/phpBB/phpbb/session.php +++ b/phpBB/phpbb/session.php @@ -959,48 +959,47 @@ class session $this->time_now = time(); } - // Firstly, delete guest sessions - $sql = 'DELETE FROM ' . SESSIONS_TABLE . ' - WHERE session_user_id = ' . ANONYMOUS . ' - AND session_time < ' . (int) ($this->time_now - $config['session_length']); - $db->sql_query($sql); - - // Get expired sessions, only most recent for each user - // Inner SELECT gets most recent expired sessions for unique session_user_id - // Outer SELECT gets session_page for them - $sql = 'SELECT s1.session_page, s1.session_user_id, s1.session_time AS recent_time + // Get expired sessions, only most recent for each registered user + // Inner SELECT gets most recent expired sessions data for unique session_user_id + // Outer SELECT gets also session_page for them + $sql_select = '( + SELECT s1.session_page, s1.session_user_id, s1.session_time AS recent_time FROM ' . SESSIONS_TABLE . ' AS s1 INNER JOIN ( SELECT session_user_id, MAX(session_time) AS recent_time FROM ' . SESSIONS_TABLE . ' WHERE session_time < ' . ($this->time_now - (int) $config['session_length']) . ' + AND session_user_id <> ' . ANONYMOUS . ' GROUP BY session_user_id ) AS s2 ON s1.session_user_id = s2.session_user_id - AND s1.session_time = s2.recent_time'; - $result = $db->sql_query($sql); - - $del_user_id = array(); - - while ($row = $db->sql_fetchrow($result)) - { - $sql = 'UPDATE ' . USERS_TABLE . ' - SET user_lastvisit = ' . (int) $row['recent_time'] . ", user_lastpage = '" . $db->sql_escape($row['session_page']) . "' - WHERE user_id = " . (int) $row['session_user_id']; - $db->sql_query($sql); - - $del_user_id[] = (int) $row['session_user_id']; + AND s1.session_time = s2.recent_time + ) AS s3'; + + // Update user session data from above selected result + switch ($db->get_sql_layer()) + { + case 'sqlite3': + case 'mysqli': + $sql = 'UPDATE ' . USERS_TABLE . " AS u, + $sql_select + SET u.user_lastvisit = s3.recent_time, u.user_lastpage = s3.session_page + WHERE u.user_id = s3.session_user_id"; + break; + + default: + $sql = 'UPDATE ' . USERS_TABLE . " + SET user_lastvisit = s3.recent_time, user_lastpage = s3.session_page + FROM $sql_select + WHERE user_id = s3.session_user_id"; + break; } - $db->sql_freeresult($result); + $db->sql_query($sql); - if (count($del_user_id)) - { - // Delete expired sessions - $sql = 'DELETE FROM ' . SESSIONS_TABLE . ' - WHERE ' . $db->sql_in_set('session_user_id', $del_user_id) . ' - AND session_time < ' . ($this->time_now - $config['session_length']); - $db->sql_query($sql); - } + // Delete all expired sessions + $sql = 'DELETE FROM ' . SESSIONS_TABLE . ' + WHERE session_time < ' . ($this->time_now - $config['session_length']); + $db->sql_query($sql); // Update gc timer $config->set('session_last_gc', $this->time_now, false); |