I replaced the separate comment counter for each post with this combined query:
do_sql('select *, count(comment_id)Result: 27 queries taking 0.01 seconds, 121 records returned.
from mt_entry, mt_author
left join mt_comment
on comment_entry_id = entry_id
where entry_blog_id = %s and
entry_status = 2 and
author_id = entry_author_id
group by entry_created_on desc
limit 20 offset %s',
(tags['page.blog'],(page-1)*20))
20 down, 20 to go.
Okay, I made the comment routine a little smarter: Don't query the database for the comments for a post when you already know there aren't any.
Result: 19 queries taking 0.01 seconds, 111 records returned. (There are less records now because a post with 10 comments have been bumped off the front page while I was testing.)
Posted by Pixy Misa at June 14, 2006 11:49 AM | TrackBackOr maybe not. That ran really, really slow when I tried it during a database backup.
I need to get a proper Minx test environment set up, pronto.
Posted by Pixy Misa at June 14, 2006 01:43 PMMy experience with joins in mySQL is that they are really slow - slower than multiple queries. Don't know how general that result is, though.
