SQL queries within a loop

Question

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 site_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.

Answer 1

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.

Answer 2

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