On Thursday 20 June 2002 05:01 pm, you wrote: > On Thu, 20 Jun 2002 15:23:53 +0200, Denis <[EMAIL PROTECTED]> 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)
Hi Manfred, Thanks for your response. > 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? I will try out patching the DB abstraction layer to use this if indeed the query allows it. (I am unsure whether this query will sometimes have more IDs inside the IN. > Also create an index on phpbb_search_wordmatch.post_id. I will try this too ! > 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? Here is the information : word_id is indeed unique in phpbb_search_wordlist. (post_id, word_id) should be unique in phpbb_search_wordmatch if the application is correctly written. It is supposed to serve as a relational lookup table for resolving search queries into a list of words with matching words inside. Rows in related tables : phpbb_search_wordmatch : 2907191 phpbb_search_wordlist : 118306 phpbb_posts : 70953 VACUUM, VACUUM ANALYZE is performed nightly, maybe this would help to do more often ? -- Denis Braekhus - ABC Startsiden AS http://www.startsiden.no ---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
