is published and produced by Matt Thommes - an independent publishing enthusiast, mobile blogger, content creator, informative writer, web developer from Chicago.
Never one to conform, Matt intends to promote the effect the web has on our lives, in an effort to intensify, instruct, and clarify all that is happening around us.
Optimizing SQL queries can become quite confusing. That’s when SQL has taken over your life.
You are at the feedback permalink page for: SQL: Think Optimize!
# (1 of 1): Dale » bluetrait.com
45 minutes after the fact. (Sat 05 Feb 2005, 6:10 PM CST)
Optimizing SQL queries is important.
I use left join a lot to join two tables together for certain information.
For example. On my blog site in the bottom right of each post there is the number of comments for that post. These comments are stored in a different table, but I want to get the information out in one query.
This is what I do (cut down version):
$query = '
SELECT posts.*, COUNT(comments.post_id)
AS count
FROM posts LEFT JOIN comments
ON posts.post_id = comments.post_id
WHERE (posts.version = 'published')
AND posts.blog_id = 1;
Also I use it for my RSS comments. You mainly want the data from the comments table but you also want the main name of the post, and it's link.
Also you should try and use SQL for things such as sorting and if possible DATE calculations, because they are much faster than doing with PHP. It also means that the array you are passing back to PHP is smaller.
So the point is, to try can get as much done as possible with one query. While keeping the PHP load as low as possible.