Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.
Moderator: General Moderators
Citizen
Forum Contributor
Posts: 300 Joined: Wed Jul 20, 2005 10:23 am
Post
by Citizen » Wed Mar 08, 2006 12:38 pm
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?
feyd
Neighborhood Spidermoddy
Posts: 31559 Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA
Post
by feyd » Wed Mar 08, 2006 12:42 pm
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 » Wed Mar 08, 2006 12:44 pm
How do I combine them? Do I have to seperate the queries with a delimiter?
feyd
Neighborhood Spidermoddy
Posts: 31559 Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA
Post
by feyd » Wed Mar 08, 2006 12:50 pm
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 » Wed Mar 08, 2006 1:01 pm
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 » Wed Mar 08, 2006 1:08 pm
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 » Wed Mar 08, 2006 1:27 pm
Thanks!