2
\$\begingroup\$

I somehow acquired the idea that I should reduce the number of trips to the database as much as possible and so I use a lot of very long queries.

For example, I had a function that does this:

$shelf = get_shelves($shop_id)
foreach($shelf)
{
    $weight = get_weight($shelf);
}

Everytime a user "view shop" it will list all the shelves, and when I see the loop, I thought I should "optimise" this, so that I only go to the database once. But I ended up with a long query:

$shelf = "SELECT * FROM shelf WHERE shop_id=?";
$weight = "SELECT tray.shelf_id, SUM(egg.weight) AS total_weight FROM tray LEFT JOIN egg WHERE egg.tray_id=tray.tray_id GROUP BY shelf_id";        
$query = "SELECT * FROM ($shelf) AS shelf LEFT JOIN ($weight) AS weight ON shelf.shelf_id=weight.shelf_id";

With this, I didn't have to get_weight() anymore.

Is this "optimisation" at all, is it acceptable? Is there a rule of thumb so that programmers and DBA can be happy at the same time?

\$\endgroup\$
  • \$\begingroup\$ If this is an optimization can only be decided by profiling. \$\endgroup\$ – MrSmith42 Apr 23 '14 at 4:49
  • \$\begingroup\$ @MrSmith42 maybe "Optimisation" is not a good word. Maybe what I am looking for are some pitfall to look out for. \$\endgroup\$ – Jake Apr 23 '14 at 5:17
  • \$\begingroup\$ Somehow, I can't believe, that the complex query with subselects will be faster than those 2 simple queries. Could you show us some results please? For example running 2 simple queries took ?? ms and running 1 complex query took ?? ms. \$\endgroup\$ – user40964 Apr 24 '14 at 10:29
4
\$\begingroup\$

You are on the right track about trying to reduce trips to the database. I think the rule of thumb would be: whatever gives the best overall performance ;-)

A single query will almost always outperform N queries. Sometimes you may have the choice between two single queries: one that's extremely complex but loads exactly the data you need, and another that's simple but loads more data than you need and you have to filter the result yourself in code. When the database is heavily loaded, the simple query can be faster.

That said, your final $query is not optimal. This would be better:

SELECT col1, col2 FROM shelf
LEFT JOIN (
    SELECT tray.shelf_id, SUM(egg.weight) AS total_weight FROM tray
    LEFT JOIN egg USING (tray_id)
    GROUP BY shelf_id
) AS weight USING (shelf_id)
WHERE shop_id = ?

Of course col1 and col2 are not real columns, I just wanted to emphasize that you should always write the column names explicitly. It's faster to load only what you really need. It also protects you from changes in the column order.

Also notice that I removed one sub-query, which should also make it faster.

\$\endgroup\$
2
\$\begingroup\$

I somehow acquired the idea that I should reduce the number of trips to the database as much as possible and so I use a lot of very long queries.

This basically says, that you are worried about response time of database. This matters when the database is not on the same machine as your application, but your application is for example in USA and your database is in Australia. In this case it might be (but does not have to be) better to use one complex query instead of a couple of simple queries.

Optimization of your queries should be done in that way that you actually measure time the queries take to execute. There is no golden rule like: "putting all simple queries into one complex query is always better". The result of such "rule" can be:

  • overly complex query
  • slow query
  • queries which are hard to maintain
  • queries which are hard to understand by other developers

Janos mentioned a good point with using SELECT col1, col2, ..., you can find more in depth information about it here: Why is SELECT * considered harmful?

\$\endgroup\$
  • \$\begingroup\$ I deal with applications where the app is in the Americas and the DBs are in Australia. They are not my worst performers. That accolade goes to the chatty apps which constantly bombard the server for the same reference data again and again and again and again. Number of calls does matter; but it is only one consideration. \$\endgroup\$ – Michael Green May 18 '14 at 11:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.