"Heavy" queries

[Lea Verou]

How do you know whether a query in your code is too heavy for the server?
What are the criteria?

[Kirk Y]

If you enable debug mode on your board you can see the types of queries being performed, how long they take to execute, and the amount of memory exhausted in executing them.

[Lea Verou]

Yes but that would depend on my server ...right?
A hack will be installed in different systems, so I was wondering if there are any "general rules".
For instance a "SELECT * FROM ..." would be bad if the table had too many records? If the query returned too many records? And if so, how many records would be considered "a lot"?

[Dismounted]

Really depends on the server.

[Adrian Schneider]

A few tips...

• USE "EXPLAIN" to see detailed info on queries
• Only select fields you need, avoid SELECT *
• Use indexes (to figure out where you need them, refer to EXPLAIN) for where clauses and ordering.
• Good DB is key! read up on database normalization
• Try to narrow down results as much as you can instead of relying on LIMIT. If you query and sort 1 million rows, and then limit the query to 10, it discards the 999,990 rows after doing all that work. Very slow... example: only select recent items using a date column (last month, say).
• Careful with subqueries! poorly written ones wil be executed on every row returned instead of just once.

As for testing if it's "too" heavy... compare the time executed with other queries. If a page is taking 30 seconds to load, it's too heavy.

[Lea Verou]

SirAdrian, if I do need all fields (exept one perhaps) why shouldn't I use SELECT *?

Also, is there a case that 2 queries are better than one?
Because currently I try just to limit the number of queries and I'm not sure I'm doing the right thing...

[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;

[Lea Verou]

Oh, no matter what I've read about these evil JOINS I was never able to understand how they work It's perhaps the only thing that I had such great difficulty understanding it

[Princeton]

The EXPLAIN data provides a lot of useful information and can help you in optimizing your queries.

The key is to learn how to interpret the EXPLAIN data.

TIP:

When creating any modification, query, etc, you should always build for large sites. This will help reduce problems and/or inquiries during the cycle of your modification.

"Testing" your modification on a small test site does not help .. your test site should have MANY members, MANY posts, MANY x rows so that you can actually see how effective your queries are.

Yes, it does help if you have access to a Big Board.
You can always create a script that will add fake members, fake posts, fake x etc for testing purposes.

Oh, no matter what I've read about these evil JOINS I was never able to understand how they work It's perhaps the only thing that I had such great difficulty understanding it

SirAdrian posted some excellent advice. :up: It's time to start learning.