Update them all. It is easier (and faster) to do that even if none of them has changed than it is to try and figure out which one changed. MySQL doesn't do anything if the row/column hasn't changed, so there is only the overhead of a connection, which is already there since you are doing the fetch/query. Being lazy actually has its benefits in this case. Doing the fetch and then testing if you should update is actually more taxing on the DB (and the server for that matter if we are talking in CPU cycles) than it would be to just do an update on all columns with a where clause on an indexed (or preferably primary keyed) column. Peter On Sun, 10 Nov 2002, Leif K-Brooks wrote: > Yes, I know that one query is more efficient than 3, but I'm trying to > do a mysql_fetch_array that automatically changes the row at the end of > the script if it has changed. I'm trying to decide whether to make it > only update the changed rows or be lazy and make it update them all if > only 1 has changed. > > Peter Beckman wrote: > > >Straight from the MySQL Documentation, which is where you should look first > >always: > > > >http://www.mysql.com/documentation/mysql/bychapter/manual_MySQL_Optimisation.html#Update_speed > > > > 5.2.10 Speed of UPDATE Queries > > > > Update queries are optimised as a SELECT query with the additional > > overhead of a write. The speed of the write is dependent on the size of > > the data that is being updated and the number of indexes that are > > updated. Indexes that are not changed will not be updated. > > > > Also, another way to get fast updates is to delay updates and then do > > many updates in a row later. Doing many updates in a row is much > > quicker than doing one at a time if you lock the table. > > > > Note that, with dynamic record format, updating a record to a longer > > total length may split the record. So if you do this often, it is very > > important to OPTIMIZE TABLE sometimes. See section 4.5.1 OPTIMIZE TABLE > > Syntax. > > > >If you aren't doing an update of 50K per row, updating 3 columns instead of > >1 will be "much quicker" (quoted from the manual). If you are doing 10,000 > >updates, read more of the manual on the syntax of UPDATE because you can > >delay your updates which allows MYSQL to update the table at its leisure > >which offers better performance. > > > >Peter, who reminds you to always read the manual (or less kindly RTFM!). > > > > > >On Sun, 10 Nov 2002, Leif K-Brooks wrote: > > > > > > > >>I'm wondering how significant the performance differences between: > >>mysql_query("update table set col1='val1' where whatever='whatever'"); > >>and > >>mysql_query("update table set col1='val1',col2='val2',col3='val3'... > >>where whatever='whatever'"); > >> > >>-- > >>The above message is encrypted with double rot13 encoding. Any unauthorized attempt to decrypt it will be prosecuted to the full extent of the law. > >> > >> > >> > >>-- > >>PHP Database Mailing List (http://www.php.net/) > >>To unsubscribe, visit: http://www.php.net/unsub.php > >> > >> > >> > > > >--------------------------------------------------------------------------- > >Peter Beckman Systems Engineer, Fairfax Cable Access Corporation > >beckman@purplecow.com http://www.purplecow.com/ > >--------------------------------------------------------------------------- > > > > > > > > > > -- > The above message is encrypted with double rot13 encoding. Any unauthorized attempt to decrypt it will be prosecuted to the full extent of the law. > > > --------------------------------------------------------------------------- Peter Beckman Systems Engineer, Fairfax Cable Access Corporation beckman@purplecow.com http://www.purplecow.com/ --------------------------------------------------------------------------- -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
