Re: slow DELETE queries

On Thu, 20 Jun 2002 15:23:53 +0200, Denis <denis(at)startsiden(dot)no> wrote:
>I traced the queries slowing it all down to this snippet in the debug log:
>DELETE FROM phpbb_search_wordlist WHERE word_id IN (
>SELECT word_id FROM phpbb_search_wordmatch WHERE word_id IN (
>SELECT word_id FROM phpbb_search_wordmatch WHERE post_id IN (70535)
>GROUP BY word_id) GROUP BY word_id HAVING COUNT(word_id) = 1)

Denis,

IN is known to be problematic; try to use EXISTS or =, wherever
possible. Can you rewrite your innermost where clause to WHERE
post_id = 70535?

Also create an index on phpbb_search_wordmatch.post_id.

If it's still too slow, give us some more information:
Is word_id unique in phpbb_search_wordlist?
Is (post_id, word_id) unique in phpbb_search_wordmatch?
How many rows are in your tables?

Servus
Manfred