- Performance improvement: made 'sid' the primary key of the sessions table.

  That should improve performance of session handling as well improve
  performance of the "Who's online"-block.  Drupal.org's sessions table
  contains appr. 40.000 sessions on a slow day and rendering the "Who's
  online"-block became a performance bottleneck.

  This change has yet to be tested on a busy site so things might go wrong.

5 files changed, 16 insertions, 5 deletions

diff --git a/database/database.mysql b/database/database.mysql
index f8be88bbc..7e8478852 100644
--- a/database/database.mysql
+++ b/database/database.mysql
@@ -567,7 +567,7 @@ CREATE TABLE sessions (
   timestamp int(11) NOT NULL default '0',
   session longtext,
   KEY uid (uid),
-  KEY sid (sid),
+  PRIMARY KEY sid (sid),
   KEY timestamp (timestamp)
 ) TYPE=MyISAM;
 
diff --git a/database/updates.inc b/database/updates.inc
index e135ae431..148119757 100644
--- a/database/updates.inc
+++ b/database/updates.inc
@@ -86,7 +86,8 @@ $sql_updates = array(
   "2004-09-17" => "update_107",
   "2004-10-16" => "update_108",
   "2004-10-18" => "update_109",
-  "2004-10-31: first update since Drupal 4.5.0 release" => "update_110"
+  "2004-10-31: first update since Drupal 4.5.0 release" => "update_110",
+  "2004-11-07" => "update_111"
 );
 
 function update_32() {
@@ -1966,6 +1967,16 @@ function update_110() {
   return $ret;
 }
 
+function update_111() {
+  $ret = array();
+
+  if ($GLOBALS['db_type'] == 'mysql') {
+    $ret[] = update_sql('ALTER TABLE {sessions} ADD PRIMARY KEY sid (sid)');
+  }
+
+  return $ret;
+}
+
 function update_sql($sql) {
   $edit = $_POST["edit"];
   $result = db_query($sql);
diff --git a/includes/session.inc b/includes/session.inc
index 91351d7d4..5155dab7f 100644
--- a/includes/session.inc
+++ b/includes/session.inc
@@ -25,8 +25,8 @@ function sess_read($key) {
   $result = db_query_range("SELECT u.*, s.* FROM {users} u INNER JOIN {sessions} s ON u.uid = s.uid WHERE s.sid = '%s' AND u.status < 3", $key, 0, 1);
 
   if (!db_num_rows($result)) {
-    $result = db_query("SELECT u.* FROM {users} u WHERE u.uid = 0");
     db_query("INSERT INTO {sessions} (uid, sid, hostname, timestamp) values(%d, '%s', '%s', %d)", $user->uid, $key, $_SERVER["REMOTE_ADDR"], time());
+    $result = db_query("SELECT u.* FROM {users} u WHERE u.uid = 0");
   }
 
   $user = db_fetch_object($result);
diff --git a/modules/user.module b/modules/user.module
index 7c5ca77be..d6ee673df 100644
--- a/modules/user.module
+++ b/modules/user.module
@@ -563,7 +563,7 @@ function user_block($op = 'list', $delta = 0, $edit = array()) {
           $time_period = variable_get('user_block_seconds_online', 2700);
 
           // Perform database queries to gather online user lists.
-          $guests = db_fetch_object(db_query('SELECT COUNT(DISTINCT(sid)) AS count FROM {sessions} WHERE timestamp >= %d AND uid = 0', time() - $time_period));
+          $guests = db_fetch_object(db_query('SELECT COUNT(sid) AS count FROM {sessions} WHERE timestamp >= %d AND uid = 0', time() - $time_period));
           $users = db_query('SELECT DISTINCT(uid), MAX(timestamp) AS max_timestamp FROM {sessions} WHERE timestamp >= %d AND uid != 0 GROUP BY uid ORDER BY max_timestamp DESC', time() - $time_period );
           $total_users = db_num_rows($users);
 
diff --git a/modules/user/user.module b/modules/user/user.module
index 7c5ca77be..d6ee673df 100644
--- a/modules/user/user.module
+++ b/modules/user/user.module
@@ -563,7 +563,7 @@ function user_block($op = 'list', $delta = 0, $edit = array()) {
           $time_period = variable_get('user_block_seconds_online', 2700);
 
           // Perform database queries to gather online user lists.
-          $guests = db_fetch_object(db_query('SELECT COUNT(DISTINCT(sid)) AS count FROM {sessions} WHERE timestamp >= %d AND uid = 0', time() - $time_period));
+          $guests = db_fetch_object(db_query('SELECT COUNT(sid) AS count FROM {sessions} WHERE timestamp >= %d AND uid = 0', time() - $time_period));
           $users = db_query('SELECT DISTINCT(uid), MAX(timestamp) AS max_timestamp FROM {sessions} WHERE timestamp >= %d AND uid != 0 GROUP BY uid ORDER BY max_timestamp DESC', time() - $time_period );
           $total_users = db_num_rows($users);