The following bug are present inside phpBB2/search.php
and also in phpBB2/includes/usercp_register.php

### (0) Original PHPBB MySQL queries
###
### Compatible : MySQL 3.23, 4.0, 4.1, 5.0, 5.1 
### !!!! BUGGED ON MySQL 5.0, 5.1 !!!!
### See http://bugs.mysql.com/bug.php?id=15872
### See http://bugs.mysql.com/bug.php?id=17019
###
SELECT session_id FROM phpbb_sessions;         
DELETE FROM phpbb_search_results 
WHERE 
   session_id NOT IN ('<SESSION_ID1>','<SESSION_ID2>',...,'<SESSION_IDN>');

### (1) First alternative require MySQL >5 with SQL-92-Standard subquery
###
### Compatible : MySQL 4.1, 5.0, 5.1 (but not available on 3.23, 4.0)
###

DELETE FROM phpbb_search_results 
WHERE 
   session_id NOT IN (SELECT session_id FROM phpbb_sessions);

### (2) Second alternative compatible with MySQL <5.0 with USING 
###
### Compatible : MySQL 4.1, 5.0, 5.1 (but not compatible 3.23, 4.0)
###

DELETE FROM r 
USING phpbb_search_results AS r 
LEFT JOIN phpbb_sessions AS s 
   ON s.session_id = r.session_id  
WHERE 
   s.session_id IS NULL;

### (3) Last good/best alternative is using Transact SQL extension
###
### Compatible : MySQL 3.23, 4.0, 4.1, 5.0, 5.1
###

DELETE phpbb_search_results 
FROM phpbb_search_results 
LEFT JOIN phpbb_sessions 
   ON phpbb_sessions.session_id = phpbb_search_results.session_id 
WHERE 
   phpbb_sessions.session_id IS NULL;           

### CONCLUSION

(3) is compatible with all MySQL version and avoid creating an 
hudge MySQL delete query (>500k if 13000 records in _sessions)
who kill in about one minute MySQL 5.0 until the bug is corrected.

###
### phpBB2/baco_search.php
###

$table_prefix = 'phpbb_';
define('SEARCH_TABLE', $table_prefix.'search_results');
define('SESSIONS_TABLE', $table_prefix.'sessions');

//
// Finish building query (for all combinations)
// and run it ...
//
$sql  = "DELETE " . SEARCH_TABLE;
$sql .= " FROM " . SEARCH_TABLE;
$sql .= " LEFT JOIN " . SESSIONS_TABLE . " ON ";
$sql .= SESSIONS_TABLE . ".session_id = ". SEARCH_TABLE . ".session_id";
$sql .= " WHERE ". SESSIONS_TABLE . ".session_id IS NULL";
printf("DEBUG-SQL: %s\n", $sql);

$ php baco_search.php
DEBUG-SQL: DELETE phpbb_search_results FROM phpbb_search_results LEFT JOIN phpbb_sessions ON phpbb_sessions.session_id = phpbb_search_results.session_id WHERE phpbb_sessions.session_id IS NULL

###
### phpBB2/includes/baco_usercp_register.php
### 

$table_prefix = 'phpbb_';
define('CONFIRM_TABLE', $table_prefix.'confirm');
define('SESSIONS_TABLE', $table_prefix.'sessions');

$sql  = 'DELETE ' .  CONFIRM_TABLE;
$sql .= ' FROM ' .  CONFIRM_TABLE;
$sql .= ' LEFT JOIN ' . SESSIONS_TABLE .' ON ';
$sql .= SESSIONS_TABLE .'.session_id = '. CONFIRM_TABLE .'.session_id';
$sql .= ' WHERE ' . SESSIONS_TABLE .'.session_id IS NULL';
printf("DEBUG-SQL: %s\n", $sql);

$ php baco_usercp_register.php
DEBUG-SQL: DELETE phpbb_confirm FROM phpbb_confirm LEFT JOIN phpbb_sessions ON phpbb_sessions.session_id = phpbb_confirm.session_id WHERE phpbb_sessions.session_id IS NULL

### The End - That's all folk
### Contact: Guy Baconniere / Infomaniak Network SA
### E-mail: < baco (g) infomaniak.ch >
### Web: http://www.infomaniak.ch/

The proposal patch for PHPBB2 follows

--- phpBB2/includes/usercp_register.php	2005-12-30 11:38:19.000000000 +0100
+++ phpBB2/includes/usercp_register.php	2006-02-02 15:58:56.071835000 +0100
@@ -942,30 +942,15 @@
 	$confirm_image = '';
 	if (!empty($board_config['enable_confirm']) && $mode == 'register')
 	{
-		$sql = 'SELECT session_id 
-			FROM ' . SESSIONS_TABLE; 
-		if (!($result = $db->sql_query($sql)))
-		{
-			message_die(GENERAL_ERROR, 'Could not select session data', '', __LINE__, __FILE__, $sql);
-		}
-
-		if ($row = $db->sql_fetchrow($result))
+		$sql  = 'DELETE ' .  CONFIRM_TABLE;
+		$sql .= ' FROM ' .  CONFIRM_TABLE;
+		$sql .= ' LEFT JOIN ' . SESSIONS_TABLE .' ON ';
+		$sql .= SESSIONS_TABLE .'.session_id = '. CONFIRM_TABLE .'.session_id';
+		$sql .= ' WHERE ' . SESSIONS_TABLE .'.session_id IS NULL';
+		if (!$db->sql_query($sql))
 		{
-			$confirm_sql = '';
-			do
-			{
-				$confirm_sql .= (($confirm_sql != '') ? ', ' : '') . "'" . $row['session_id'] . "'";
-			}
-			while ($row = $db->sql_fetchrow($result));
-		
-			$sql = 'DELETE FROM ' .  CONFIRM_TABLE . " 
-				WHERE session_id NOT IN ($confirm_sql)";
-			if (!$db->sql_query($sql))
-			{
-				message_die(GENERAL_ERROR, 'Could not delete stale confirm data', '', __LINE__, __FILE__, $sql);
-			}
+			message_die(GENERAL_ERROR, 'Could not delete stale confirm data', '', __LINE__, __FILE__, $sql);
 		}
-		$db->sql_freeresult($result);
 
 		$sql = 'SELECT COUNT(session_id) AS attempts 
 			FROM ' . CONFIRM_TABLE . " 
--- phpBB2/search.php	2005-12-30 11:38:20.000000000 +0100
+++ phpBB2/search.php	2006-02-02 15:59:37.891835000 +0100
@@ -632,25 +632,14 @@
 		// Finish building query (for all combinations)
 		// and run it ...
 		//
-		$sql = "SELECT session_id 
-			FROM " . SESSIONS_TABLE;
-		if ( $result = $db->sql_query($sql) )
+		$sql  = "DELETE " . SEARCH_TABLE;
+		$sql .= " FROM " . SEARCH_TABLE;
+		$sql .= " LEFT JOIN " . SESSIONS_TABLE . " ON ";
+		$sql .= SESSIONS_TABLE . ".session_id = ". SEARCH_TABLE . ".session_id";
+		$sql .= " WHERE ". SESSIONS_TABLE . ".session_id IS NULL";
+		if ( !$result = $db->sql_query($sql) )
 		{
-			$delete_search_ids = array();
-			while( $row = $db->sql_fetchrow($result) )
-			{
-				$delete_search_ids[] = "'" . $row['session_id'] . "'";
-			}
-
-			if ( count($delete_search_ids) )
-			{
-				$sql = "DELETE FROM " . SEARCH_TABLE . " 
-					WHERE session_id NOT IN (" . implode(", ", $delete_search_ids) . ")";
-				if ( !$result = $db->sql_query($sql) )
-				{
-					message_die(GENERAL_ERROR, 'Could not delete old search id sessions', '', __LINE__, __FILE__, $sql);
-				}
-			}
+			message_die(GENERAL_ERROR, 'Could not delete old search id sessions', '', __LINE__, __FILE__, $sql);
 		}
 
 		//