List:General Discussion« Previous MessageNext Message »
From:Andrés Tello Date:April 8 2013 11:53pm
Subject:Re: update a row only if any column has changed, in a very large table
View as plain text  
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.neuli@stripped> wrote:

> thx all, the source data is in text file.
>
>
>
>
> ----- Original Message -----
> From: "hsv@stripped" <hsv@stripped>
> To: mysql list <mysql@stripped>
> 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
>
>

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