I have a lot of data that has to be downloaded from an API and inserted into mysql.
Here's the current setup:
1) download the data to mytable_temp
2) delete all data from mytable
3) Copy data from mytable_temp to mytable
I thought this would prevent data not showing up in users searches during updates.
However, this is causing problems:
1) If my connection breaks during an update (which can take 3 hours) udates don't complete.
2) mysql slow log is large - some visitor queries being affected during the delete and copy to live table phases.
My question is: would it be better to remove the temporary table and update the actual table real-time?
I'm concerned that if the 'red widget' row is being updated at the exact moment a user searches for a 'red widget' the data won't show.