Take a look here. http://dev.mysql.com/doc/refman/5.0/en/timestamp-initialization.html
timestamp field can be autoupdated and autoinitilizated With both DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP, the column has the current timestamp for its default value and is automatically updated to the current timestamp. CREATE TABLE t1 ( ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ); And after that, just export data > a defined timestamp. I think this is the easiest way and more straight forward... On Sat, Apr 6, 2013 at 9:49 PM, Rajeev Prasad <rp.ne...@yahoo.com> wrote: > thx all, the source data is in text file. > > > > > ----- Original Message ----- > From: "h...@tbbs.net" <h...@tbbs.net> > To: mysql list <mysql@lists.mysql.com> > Cc: > Sent: Saturday, April 6, 2013 8:02 PM > Subject: Re: update a row only if any column has changed, in a very large > table > > >>>> 2013/04/06 13:56 -0700, Rajeev Prasad >>>> > I have a table with around 2,000,000 records (15 columns). I have to sync > this from an outside source once every day. not all records are > changed/removed /new-added everyday. so what is the best way to update only > those which have changed/added/or deleted? > > i can use update_or_create but that will update (re-write the row) even if > nothing has changed in the row/record. wont that be an overhead? how can i > escape that? what would be the fastest and least resources consuming way to > do this table update? > > I also have another table with 500,000 rows and i wish to implement the > same solution to that too. > > I earlier posted this on DBIx list, as i thought i could use DBIx tools to > manage this. but based on response, it seems that MySQL tools would be more > helpful in doing it in most efficent way. Plz. advice how can i address > this. > > I also considered to delete and simply recreate the table each day..... > but changes/add and delete are not too many (may be a few hundreds.. max) > <<<<<<<< > Sounds like a case for replication (look it up: > http://dev.mysql.com/doc/refman/5.5/en/replication.html > http://dev.mysql.com/doc/refman/5.5/en/replication-formats.html). There > is statement-replication, and row-replication. The former replicates all > operations on the database, in the form wherin they were made. False > changes (changing a field to its former value), too, are recorded. The > latter records only those changes to a table that are real changes. (In > MySQL statement-replication is of earlier implementation.) > > After changes are recorded, they are passed from the master --the > wellspring of the changes-- to the slave --the taker of them. > > These are not tools, as such: replication is something implemented in the > database-management system. If both your databases are in MySQL you can get > help here. > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/mysql > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/mysql > >
