LOAD DATA INFILE on a MyISAM table blocks user read-queries, can we get around this?

Question

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?

Answer 1

If your table is a MyISAM engine you can try to specify CONCURRENT in your statement.

Load data ref.

Concurrent insert ref.

With LOAD DATA INFILE, if you specify CONCURRENT with a MyISAM table that satisfies the condition for concurrent inserts (that is, it contains no free blocks in the middle), other sessions can retrieve data from the table while LOAD DATA is executing. Use of the CONCURRENT option affects the performance of LOAD DATA a bit, even if no other session is using the table at the same time.

UPDATE FOR FREE BLOCK EXPLAIN

When you delete a row a free block can occur, if you have free blocks in the "middle" of your insert, for example if you replace deleted rows(index and/or primary key) with your load data, concurrent insert do not work, when free blocks are filled in future insert become concurrent again.