"Heavy" queries

[Adrian Schneider]

Because it uses a bit more overhead, and it's bad practice because you aren't clearly defining what you are reading from the database. It's similar to using the highest error reporting... you have way more control of what is going on, and you will spot any funny business much faster.

As for the # of queries... while this generally doesn't matter (generally, but i have worked on a site where one query * 5000 active users makes a HUGE difference!) it is good to combine them when there is a relationship between the data from the two queries. The most common is the use of joins to get results from different tables (ex: post + user table). I would have to say this depends greatly on the query. Sometimes two queries is faster if the relationship isn't a good one, which goes back to my normalization comment.

Some common improvements you can make:

SELECT * FROM messages LIMIT 10;

SELECT id, body, dateline
FROM messages
WHERE dateline >= X
ORDER BY dateline DESC
LIMIT 10

X depending greatly on the number of records in the table. If it's VERY full (100 records per day) then you can probably default it to show records from the past day rather than scanning the entire table. It uses the indexed (hopefully!) dateline column to narrow results very quickly. Instead of having to query 1 million rows, and then discard them all, it will use the index (fast) and only return say a 100 rows (depending on how you narrow it down) and then chop the results to 10.

SELECT * FROM messages WHERE id = 5;

+

SELECT * FROM user WHERE userid = $message[userid];

=

SELECT
    messages.id,
    messages.body,
    messages.dateline,
    messages.userid,
    user.username
FROM messages
LEFT JOIN user USING (userid)
WHERE messages.id = 5;