Simultaneous LOAD DATA INFILE and SELECT

MySQL & MariaDB Other MySQL® Questions
1

Hi all,

When LOAD DATA INFILE query is executed if SELECT from same_table is executed, the SELECT query is locked. I want to run both queries simultaneously. How can I achieve that.

Thanks,

2

Hi cowboymathu,

What storage engine is your table? In MyIsam you can use LOAD DATA CONCURRENT INFILE to allow concurrent reads from the table while you are inserting into it. This can slow the performance of LOAD DATA especially with large amounts of data to load and also may lead to inconsistent reads from the table. More here: [URL]http://dev.mysql.com/doc/refman/5.0/en/load-data.html[/URL]

An alternative would be: create a copy of the table you want to load data to load data into the copy, swap table names when load is done.

Hope this helps.

3

Hi lyolechka,

Thanks for your reply. Yes, I am using MyISAM type with partitioning. Currently I am using LOAD DATA CONCURRENT INFILE syntax but seems it does not allow me to do simultaneous SELECT.

Your alternative idea seems better but in my instance I am using table with both partition and sub partition and which has almost 1 billion (1000 millions) of records. It eats more than 400 GB in capacity. So making copy is not feasible in this.

Any better way in my case?