mu.nu - "My Tamagotchi!" she said. "It has to poop."

June 14, 2006

Optimising SQL For Fun And Profit, Part The First

I replaced the separate comment counter for each post with this combined query:

do_sql('select *, count(comment_id)
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))
Result: 27 queries taking 0.01 seconds, 121 records returned.

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 11:49 AM | Comments (12) | Add Comment | Trackbacks (Suck)

1 Or 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 PM

2 My experience with joins in mySQL is that they are really slow - slower than multiple queries. Don't know how general that result is, though.

Posted by: David Boxenhorn @ at June 15, 2006 09:52 AM

3 I appreciate theI appreciate the ideas and information you provided. I ideas and information you provided.

Posted by: Jordan Sneakers Sale @ at April 18, 2011 05:55 AM

4
buy cheap louboutin sandals when people are looking into the Win a new net on September 21 and give or get an electric shock according to"place agency" briefing,christian louboutin heels the disaster Sun amount of money first quote is 251,660,000

Posted by: Wholesale blog @ at May 20, 2011 03:25 AM

Hide Comments | Add Comment






Processing 0.01, elapsed 0.0067 seconds.
22 queries taking 0.0046 seconds, 26 records returned.
Page size 8 kb.
Powered by Minx 0.8 beta.