Multiple updates with one query

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Citizen
Forum Contributor
Posts: 300
Joined: Wed Jul 20, 2005 10:23 am

Multiple updates with one query

Post 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?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
Citizen
Forum Contributor
Posts: 300
Joined: Wed Jul 20, 2005 10:23 am

Post by Citizen »

How do I combine them? Do I have to seperate the queries with a delimiter?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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)
Citizen
Forum Contributor
Posts: 300
Joined: Wed Jul 20, 2005 10:23 am

Post 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");
Last edited by Citizen on Wed Mar 08, 2006 1:26 pm, edited 1 time in total.
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Post 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
Citizen
Forum Contributor
Posts: 300
Joined: Wed Jul 20, 2005 10:23 am

Post by Citizen »

Thanks!
Post Reply