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?

1 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.


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.

  • What does "no free blocks in the middle" mean? Mar 18, 2013 at 9:06
  • 1
    Hi David, see my update Mar 18, 2013 at 20:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.