Optimization!

[Anon]

Well, I've never had to worry about this before. Now I have high traffic and it's of more importance than I could have imagined.

Anyway, I need to know some principles for ensuring that queries run as quickly as possible. I ran my own "stress test" on my code (set it up to log query return values and hold F5 for 30 seconds) and an unacceptable number of queries failed to finish.

I guess I believe my table structure and queries are as efficient as they can be (yes, I'm indexing all the columns on which I search or sort - yes, I'm properly using JOIN statements and grouping rows where appropriate), but I want to know if more small queries might be better than fewer large queries? It seems the ones that fail are the really long ones, or the ones that have a lot of work to do. I could just use some hints on how to improve the performance.

[Anon]

here's a great example. This query appears to be one that takes a bit longer than most of the rest. I know that HAVING clauses have no optimization, but I can't think of any way to do what I'm doing here without a HAVING clause:

SELECT ctopics.topicId, MAX(cmessages.postTime) as lastReply from ctopics left join cmessages using (topicId) GROUP BY topicId HAVING lastReply<'$lastReply'