Very slow SQL Query, not even working for very large sites...

In the search module there is a part with this code:

// Find words that were deleted from search_index, but are still in
// search_total. We use a LEFT JOIN between the two tables and keep only the
// rows which fail to join.
$result = db_query("SELECT t.word AS realword, i.word FROM {search_total} t LEFT JOIN {search_index} i ON t.word = i.word WHERE i.word IS NULL");
while ($word = db_fetch_object($result)) {
db_query("DELETE FROM {search_total} WHERE word = '%s'", $word->realword);
}

I found the first used query (SELECT ...) in my mysql-slow-queries-log with a execution time of more than 600 seconds on my server... The while loop was never executed due to timeouts...

I suggest to replace it with a set of queries that is much faster:

$sql = 'DROP TABLE IF EXISTS tmpsearch';
db_query($sql);
$sql = 'CREATE TABLE tmpsearch(word VARCHAR(50), del INTEGER(1) UNSIGNED)';
db_query($sql);
$sql = 'INSERT INTO tmpsearch (word, del) SELECT word, 1 FROM {search_total}';
db_query($sql);
$sql = 'ALTER TABLE tmpsearch add index i1(word)';
db_query($sql);
$sql = 'UPDATE tmpsearch t, {search_index} i SET t.del = 0 WHERE t.word = i.word';
db_query($sql);
$sql = 'ALTER TABLE tmpsearch add index i2(del)';
db_query($sql);
$sql = 'DELETE FROM tmpsearch WHERE del = 0';
db_query($sql);
$sql = 'DELETE FROM {search_total} WHERE word IN (SELECT word FROM tmpsearch)';
db_query($sql);