SQL queries within a loop

Google code suggests that you should AVOID sql queries within a loop. The reason being that the multiple round trips to the database significantly slows your scripts down. The example query they give is this.

$userData = array();
foreach ($userList as $user) {
     $userData[] = '("'.$user['first_name'].'", "'.$user['last_name'].'")';
}
$query = 'INSERT INTO users (first_name,last_name) VALUES'.implode(',',$userData);
mysql_query($query);

My questions are... 1. How important is it to keep your query out of a loop, and is it always avoidable? 2. How can you implement a SELECT statement with this same logic.

i.e. Let's say I have this query.

$index=0;
while ($index < count($id)) {
     $result[] = mysql_query("SELECT * FROM tblInfo WHERE 开发者_如何学JAVAsite_id = '".$id[$index]."' ");
     $index++;
}

How can this SELECT statement be executed outside of a loop? I have a large amount of SELECT statements that are far more complex than this. So if deemed necessary, I'd like to get these queries out of loops. If someone out there is concurring with google, could you please post some sample code.

Any response will be greatly appreciated.

---

You can use MySQL IN operator with a list of IDs.

SELECT * FROM table WHERE id IN (1,4,6,8,5,6)

It can handle even very lengthy lists of thousands of IDs (certainly better than thousand SELECTs). But in such case you also should consider design of your application. If you need to use IN with thousands of IDs on every page load, something is very wrong in you design.

INSERTs can also be condensed into one query, see documentation.

Generally, most queries in loops can be usually rewritten as subqueries. however in such case, you have to choose between performance and readability/maintainability. Subqueries generally are hell to understand and optimize/debug.

---

You might find this article interesting: http://blog.fatalmind.com/2009/12/22/latency-security-vs-performance/

---

How important avoiding the round trip cost is to you depends on a few things.

First, how many round trips are you making? If you're making 3 or 4, you can probably ignore the advice if heeding it would be painful.

Second, how costly is the round trip for you in your setup? If a roundtrip to the db takes 100 ms, that's to be avoided much more seriously than if it only takes 2 ms.

Third, how time-sensitive is the process that needs to do the queries? If you're making a user wait, you should really pay attention to this - users hate to wait! If you're using an Ajax process that runs behind-the-scenes and does some work, maybe it's less important (though you'll still have to watch out for timeouts, maybe).

Basically, Google's advice is good, in that wasted time is wasted time. However, depending on your specific case, wasted time may be more or less serious to you, your system, and your users.