If'n it were my nickel, here is how I would solve the problem (at a somewhat
high level). That is, assuming I had an ETL tool available.
1. Create landing tables for your source data.
2. Load data from the source table(s) to your new landing table(s).
3. Perform lookups from the new landing table to target to identify:
inserts, updates, deletes, "do nothings".
4. Write these status back to the landing table. (Want to separate the
inserts from the updates from the deletions.)
5. Load the 'inserts' to your target table.
6. Load the 'updates' to your target table.
7. Perform the 'deletes' on your target table.
And, one other thing that I would do is to log counts and times...so I could
go back over time and evaluate performance.
But then again, I work with ETL tools...so, that is my proverbial hammer.
And given that, everything pretty much looks like a nail.
From: Rajeev Prasad [mailto:rp.neuli@stripped]
Sent: Saturday, April 06, 2013 3:57 PM
To: mysql list
Subject: update a row only if any column has changed, in a very large table
>I have a table with around 2,000,000 records (15 columns). I have to sync
this from an outside source once everyday. 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
>I also considered to delete and simply recreate the table each day..... but
chnages/add and delete are not too many (may be a few hundreds.. max)
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql