Bad Practice: DB Queries inside loops
Sat Sep 29, 2007 at 03.37
This is something I see day in and day out, and unfortunately is a deep gash into the good reputation of otherwise awesome PHP coders. If you ever find yourself putting a database query inside a loop, stop and study the code for a long moment. Chances are this is unnecessary.
Generally speaking the only acceptable time for a query to be inside a loop is when it is contained in a script that will only be used once and that is meant to do some sort of data transfer/transformation (which can probably be better accomplished using a pure SQL solution, but that is a battle for another day.)
So let's examine one of the more common occurrences of this problem:
, SUM(points) total_points
FROM user_points
GROUP BY user_id
ORDER BY total_points DESC";
$query_1 = mysql_query($sql_1) or die(mysql_error() . "<br />" . $sql_1);
while($row_1 = mysql_fetch_assoc( $query_1 )) {
$sql_2 = "SELECT first_name
, last_name
FROM users
WHERE user_id = " . $row_1['user_id'];
$query_2 = mysql_query( $sql_2 ) or die( mysql_error() . "<br />" . $sql_2);
//more code follows
}
If you recognize this code and are thinking, "Hey man, what's wrong with that? That's how I always do it!" then I am extremely happy that you are reading this post.
The problem is that we really don't know how many users there are - and even if we did this is still a bad solution - in the user_points table and thus we are repeating the query to get the user's name an unknown number of times (N times.) This is a resource hog of a script to do something that is simple to accomplish using the proper query.
In this case, had we known about and employed an INNER JOIN, we will eliminate the subsequent calls to the database and retrieved all the information we need in one trip. This terminates the script's connection to the database quicker and allows another connection to be established by a different script. This is a good thing, trust me. If you have ever had to figure out what is causing your server to crawl you will know that I speak the truth.
So how do we solve the problem? As stated above we take that query and get the information using an INNER JOIN. Here is our new query
, u.first_name
, u.last_name
, SUM( up.points ) total_points
FROM user_points up
INNER JOIN users u
ON up.user_id = u.user_id
GROUP BY user_id
ORDER BY total_points
If any of that looks foreign to you, it's time you brushed up on your SQL. I suggest reading a few tutorials on JOINs.
- Tagged:
- technicalarticles
- phpalized
- database
- mysql
- php
No comments yet, be the first!