One big query vs. lots of small queries

Here's a question of efficiency.

I have an application in PHP using MySQL.

Users select items on a form and each selected item represents a change
in a table row. It is possible that 100s of changes might be chosen from
a single form.

Which is likely to be faster/more efficient?

multiple queries like...
$query="UPDATE some_table SET x='$y' WHERE id='$z[$key]'";
$result=mysql_query($query);

or one huge query like...
$query="UPDATE some_table SET x='$y' WHERE id='$z[$key]'
OR id='$z[$another_key]' OR id='$z[$another_key]
OR id='$z[$another_key]"; etc?
$result=mysql_query($query);

In the latter, PHP would have to construct the WHERE string, which would
require some processing power as well. Is there a limit to how many OR's
can be added to a mysql query? No - don't answer that, I'll look in the
manual.

Many thanks,

Jeff

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

RE: One big query vs. lots of small queries

I would think the one huge query (using an IN clause) would be better than
multiple queries...especially if you structure it to use an index

$query="UPDATE some_table SET x='$y' WHERE id
IN('.$z[$key].','.$z[$another_key].','.$z[$another_key]...

bastien


>From: Jeffrey
>To: PHP DB
>Subject: [PHP-DB] One big query vs. lots of small queries
>Date: Mon, 18 Dec 2006 15:23:36 +0100
>
>Here's a question of efficiency.
>
>I have an application in PHP using MySQL.
>
>Users select items on a form and each selected item represents a change in
>a table row. It is possible that 100s of changes might be chosen from a
>single form.
>
>Which is likely to be faster/more efficient?
>
>multiple queries like...
>$query="UPDATE some_table SET x='$y' WHERE id='$z[$key]'";
>$result=mysql_query($query);
>
>or one huge query like...
>$query="UPDATE some_table SET x='$y' WHERE id='$z[$key]'
> OR id='$z[$another_key]' OR id='$z[$another_key]
> OR id='$z[$another_key]"; etc?
>$result=mysql_query($query);
>
>In the latter, PHP would have to construct the WHERE string, which would
>require some processing power as well. Is there a limit to how many OR's
>can be added to a mysql query? No - don't answer that, I'll look in the
>manual.
>
>Many thanks,
>
>Jeff
>
>--
>PHP Database Mailing List (http://www.php.net/)
>To unsubscribe, visit: http://www.php.net/unsub.php
>

____________________________________________________________ _____
Enter the "Telus Mobility Xbox a Day" contest for your chance to WIN! Telus
Mobility is giving away an Microsoft Xbox® 360 every day from November 20 to
December 31, 2006! Just download Windows Live (MSN) Messenger to your
IM-capable TELUS mobile phone, and you could be a winner!
http://www.telusmobility.com/msnxbox/

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php