Bluetrait - For future reference

search Popular posts

Click here if you are looking for Bluetrait, the weblog software.

For future reference

Posted by Michael Dale on Fri, 29 Oct 2004 8:37 PM

SELECT news.*, COUNT(comments.newsid) AS count FROM news
LEFT JOIN comments ON news.id=comments.newsid
GROUP BY news.id ORDER BY Id DESC

...don't mind me.

EDIT: Excellent down to 0.16 from 0.26 seconds. And down to 1 query from a shocking 162 on my little test. Very very good.

Permalink

On Fri, 29 Oct 2004 at 9:53 PM, Matthom wrote:

Must you show off your SQL skills? Heh.

Actually, I really love the power of LEFT JOIN.

Oooh, I love SQL.

1: Comment Link

On Fri, 29 Oct 2004 at 10:01 PM, Michael Dale wrote:

Took me a while to get that query out! But it is very powerful. I've got the front page number of queries down from about 14 to 2!

Now I can get the number of comments at the same time as getting the news posts themselves. So two different tables at one time.

2: Comment Link

On Fri, 29 Oct 2004 at 10:57 PM, Matthom wrote:

Oooh, that soinds interesting. I, too, pull out everything at once - on some pages. But, like you said, I have to do a number of queries.

LEFT JOIN solves a lot of problems of multiple queries, but it acts really weird sometimes.

Also, it depends on how the tables are structured. If they are structured sloppily - like they are in my case - LEFT JOIN won't solve any problems.

The first step is solid relational database theory.

3: Comment Link