I have a CSV file on about 200 MB/1 mill rows, which is updated every night. I need the values from this file available in a MySQL table.
I get this working by using
LOAD DATA INFILE filepath REPLACE INTO TABLE tablename.
This is fast and it keep my indexes.
The problem is that values which are no longer in the csv will not be deleted.
Another attempt has been to
TRUNCATE the table, and then
LOAD DATA INFILE filepath INSERT INTO TABLE tablename afterwards. This seems to be about 25% faster. But by doing this I've had a lot of trouble with indexes being disabled(?) or stopped working. I've been reading on the MySQL docs and SO for some days now, but I can't figure that out.
My next attempt has been similar to the first. I added a column "update", and set every row to
update=0 before doing a
REPLACE INTO with a
SET update=1, and then delete all remaining rows with
update=0. This is my current method.
My question is this: Which method is considered better for completely replace a table with info from csv, but keep/refresh indexes. I'd appreciate any advice on the subject.
I am using the MyISAM engine.