First benefits from performance optimization: I have discoveredÂ “the mother of all queries” in phpBB.
I am not sure about the newest version but the installation I use in diorama contains a lot of queries like:
SELECT m.word_id FROM phpbb_search_wordmatch m, phpbb_search_wordlist w WHERE w.word_text IN ('S', 'S', 'S', 'S', 'S', 'S', 'S', 'S', 'S', 'S', 'S', 'S', 'S') AND m.word_id = w.word_id GROUP BY m.word_id HAVING COUNT(m.word_id) > N
These queries belong to the forum search logic and floods MySQL query logs up to 90%. The good news is you can get rid of this logic without any significant functional loss according to this discussion.
To do this just disable “remove_common” function in includes/functions_search.php (e.g. by putting a return-statement in the first line of it).
Already checked this. Works good. Works faster.