Page 1 of 1

Multiple updates with one query

Posted: Wed Mar 08, 2006 12:38 pm
by Citizen
Are there any performance problems with running lots of queries vs. having one query that updates multiple rows?

Reason is, one one page I need to update two different things. Do I have to use two queries?

Posted: Wed Mar 08, 2006 12:42 pm
by feyd
If you can combine them, it can be faster. But if they are unrelated, it's hard to combine the update queries.

Two queries are rarely a performance hit for a database server.

Posted: Wed Mar 08, 2006 12:44 pm
by Citizen
How do I combine them? Do I have to seperate the queries with a delimiter?

Posted: Wed Mar 08, 2006 12:50 pm
by feyd
It'd help to have context on what needs updating before I can give any "useful" information on combining them (outside of read the manual)

Posted: Wed Mar 08, 2006 1:01 pm
by Citizen
ok, here's the two queries:

Code: Select all

$sql = mysql_query("UPDATE `accounts` SET `points` = '$points' WHERE `name` = '$username' LIMIT 1");
$sql = mysql_query("UPDATE `accounts` SET `totalgiven` = '$totalgiven' WHERE `name` = '$username' LIMIT 1");

Posted: Wed Mar 08, 2006 1:08 pm
by josh

Code: Select all

UPDATE `table` SET `field` = 'value', `field2` = 'value' WHERE `field3` = 'value' LIMIT 1
A certain fraction of the time spent on your query is sending the query to the DBMS, then the DBMS has to analyze your query and find out which indexes to use, etc.. in this case you avoid doing that twice but separate updates can sometimes be faster when you get into certain types of joins. The mysql documentation has a lot of useful information on how mysql optimizes queries that is very helpful.


Edit: http://dev.mysql.com/doc/refman/5.0/en/ ... ation.html

Posted: Wed Mar 08, 2006 1:27 pm
by Citizen
Thanks!