[@mark8barnes Member 16 years, 3 months ago]

@sambauers: Let me just give you a couple of observations. Firstly, regarding the first search performed.

SELECT p.*, 0 AS search_score, MAX(post_time) AS post_time FROM bb_posts AS p JOIN bb_topics as t ON ( t.topic_id = p.topic_id ) WHERE p.post_text LIKE '%test%' AND p.post_status = '0' AND t.topic_status = '0' GROUP BY t.topic_id ORDER BY p.post_time DESC LIMIT 5;

The first thing that can be done here is remove the join and replace it with a sub-query. This way the sub-query can be cached, and speed is much improved. Here’s the idential query without the join:

SELECT p.*, 0 AS search_score, MAX(post_time) AS post_time FROM bb_posts AS p WHERE p.post_text LIKE '%test%' AND p.post_status = '0' AND NOT p.topic_id IN (SELECT t.topic_id FROM bb_topics AS t WHERE t.topic_status <> '0') GROUP BY p.topic_id ORDER BY p.post_time DESC LIMIT 5

That cuts execution time by 55% on my setup. If you have lots of deleted topics this might not apply, but if you have a huge database you ought to be cleaning the deletes out of it regularly anyway.

_ck_ is also right that ordering by post_id is quicker than by post_time. 17% quicker in my case.