I received the following notification from my hosting service:

We have disabled your database to return the server
to normal usage. To re-enable your database, you will need to correct
the following query:

-Problematic 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

EXPLAIN:
id select_type table
type possible_keys key key_len ref
rows Extra
1 SIMPLE t index
PRIMARY 152 44937 Using index
1
SIMPLE i ref word_sid_type,word word
152 drudatauser.t.word 5 Using where; Using index;
Not exists

This query examines 224685 rows, which is unacceptable in shared hosting.

I have about 12,500 nodes in my site. Discussion has focused on search indexing going awry, but I haven't been able to replicate the condition. I turned on Developer and looked at all of the queries being generated, but never saw the query referenced above.

Anybody have a lead on what this problem is and how to resolve it?

Thanks in advance.

Comments

jaredsmith’s picture

I'm seeing the same problem, on a dedicated server. This query is causing my server to come to its knees from time. (I too have a large number of nodes.)

Were you ever able to make any headway on this issue?

hbergin’s picture

http://drupal.org/node/257916

The above seems related and there are patches you may want to try.