I've got a large reference table, 100GB, in a MyISAM table, load it up from scratch goes well (4ish hours) using LOAD DATA INFILE.
But we have 1GB each day we want to update in a batch process using LOAD DATA INFILE. Unfortunately LOAD DATA INFILE blocks users from querying against that table.
Is there a way I can get around this?
For example:
- Can I load the new data into another table and insert into from that table or something similar to allow loading the data and read queries against the primary table at the same time?
- Can I do some kind of partitioning with new tables added daily, joined by a view? Would this be a bad idea with such a large table?