List:General Discussion« Previous MessageNext Message »
From:hsv Date:April 7 2013 1:02am
Subject:Re: update a row only if any column has changed, in a very
large table
View as plain text  
>>>> 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.

Thread
update a row only if any column has changed, in a very large tableRajeev Prasad6 Apr
  • RE: update a row only if any column has changed, in a very large tableJason Trebilcock7 Apr
  • Re: update a row only if any column has changed, in a verylarge tablehsv7 Apr
    • Re: update a row only if any column has changed, in a very large tableRajeev Prasad7 Apr
      • Re: update a row only if any column has changed, in a very large tableAndrĂ©s Tello8 Apr