Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?
share|improve this question
add comment

1 Answer

up vote 2 down vote accepted

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.

share|improve this answer
    
What does "no free blocks in the middle" mean? –  David Parks Mar 18 '13 at 9:06
1  
Hi David, see my update –  Cristian Porta Mar 18 '13 at 20:24
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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