Your bulk insert buffer is 4G. That's great ... FOR MyISAM !!!
InnoDB does not use the bulk insert buffer.
You may need to have sqlalchemy throttle the load data infile
calls into multiple transactions.
You may also want to disable innodb_change_buffering, setting it to inserts
.
Unfortunately, you cannot do SET GLOBAL innodb_change_buffering = 'inserts';
. If you dom you may need to set it in my.cnf and restart mysql.
UPDATE 2012-07-13 16:53 EDT
I just noticed that you have two values in the my.cnf for innodb_buffer_pool_size
. First one is 2385M, and the last one is 14G. If MySQL for Windows accepted 14G and you only have 12G of RAM, your server must be having a good old time swapping.
You can verify what the buffer pool size is with
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
UPDATE 2012-07-13 16:58 EDT
You may also want to check how full the buffer pool is with this
SELECT FORMAT(A.num * 100.0 / B.num,2) BufferPoolFullPct FROM
(SELECT variable_value num FROM information_schema.global_status
WHERE variable_name = 'Innodb_buffer_pool_pages_data') A,
(SELECT variable_value num FROM information_schema.global_status
WHERE variable_name = 'Innodb_buffer_pool_pages_total') B;
Assuming that Customer_details
table is populated you can do lookups on the fly with LOAD DATA INFILE
by leveraging session variables and a SET
clause
LOAD DATA INFILE '/path/to/order_details.txt'
INTO TABLE order_details
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(ord_id, @customer_name) -- use a session variable to store a value read from the file
SET cust_id =
(
SELECT cust_id
FROM customer_details
WHERE customer_name = @customer_name -- use a session variable to get an id
LIMIT 1 -- make sure that exactly one row is returned
)
Suppose we have a CSV file with the following content
ord_id, customer_name
1,"Customer1"
2,"Customer2"
3,"Customer2"
4,"Customer4"
Let's try import it:
mysql> CREATE TABLE customer_details(`cust_Id` int, `customer_name` varchar(32));
Query OK, 0 rows affected (0.02 sec)
mysql> INSERT INTO Customer_details (`cust_Id`, `customer_name`)
-> VALUES (1, 'Customer1'),(2, 'Customer2'),(3, 'Customer3'),(4, 'Customer4');
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> CREATE TABLE order_details (`ord_id` int, `cust_Id` int);
Query OK, 0 rows affected (0.02 sec)
mysql> LOAD DATA INFILE '/tmp/order_details.txt'
-> INTO TABLE order_details
-> FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
-> LINES TERMINATED BY '\n'
-> IGNORE 1 LINES
-> (ord_id, @customer_name) -- use a session variable to store a value read from the file
-> SET cust_id =
-> (
-> SELECT cust_id
-> FROM customer_details
-> WHERE customer_name = @customer_name -- use a session variable to get an id
-> LIMIT 1 -- this is to ensure that exactly one is returned
-> ) ;
Query OK, 4 rows affected (0.00 sec)
Records: 4 Deleted: 0 Skipped: 0 Warnings: 0
mysql> SELECT * FROM order_details o JOIN customer_details c ON o.cust_id = c.cust_id;
+--------+---------+---------+---------------+
| ord_id | cust_Id | cust_Id | customer_name |
+--------+---------+---------+---------------+
| 1 | 1 | 1 | Customer1 |
| 2 | 2 | 2 | Customer2 |
| 3 | 2 | 2 | Customer2 |
| 4 | 4 | 4 | Customer4 |
+--------+---------+---------+---------------+
4 rows in set (0.00 sec)
There you have it.
Best Answer
Insert the individual values in bulk. In other words, each row of the csv file should become a row in the database with the same number of columns.
Using Notepad++ you can use a regex find and replace to easily convert your CSV into a very large insert statement. Replace
\r\n
with'),('
and,
with','
. Tidy up the first and last line of the file, then add the actual insert statement to the front.insert into table (col1, col2, col3) values .....