Mike, What behaviour you experience depends to some extent on what storage engine you are using and on what other non-unique indexes you have on the tables.
With LOAD DATA INFILE on empty MyISAM tables all non-unique indexes are created in a separate batch which makes it much faster if you have a lot of indexes. From memory you can create the indexes faster by turning them off with 'ALTER TABLE tablename DISABLE KEYS' before the 'LOAD DATA INFILE' command and then using 'ALTER TABLE tablename ENABLE KEYS' to re-create the indexes after the LOAD DATA INFILE completes. Regards John Daisley MySQL & Cognos Contractor Certified MySQL 5 Database Administrator (CMDBA) Certified MySQL 5 Developer (CMDEV) IBM Cognos BI Developer Telephone +44 (0)7812 451238 Email j...@butterflysystems.co.uk -----Original Message----- From: mos [mailto:mo...@fastmail.fm] Sent: 17 October 2009 22:49 To: mysql@lists.mysql.com Subject: Load Data Infile quirk I'm trying to speed up Load Data Infile and after some experimenting have noticed this "qwirk". BTW, all of the tables used below are empty and have identical table structures. The value being loaded into the primary key column is 'NULL'. Test1: 246 seconds to run Load Data Infile into a table (Table1) with 1 primary autoinc column, and 2 compound keys. Test2: 69 seconds to run Load Data Infile into similar table (Table2) with no keys 111 seconds to rebuild the missing keys in Table2 69+111=180 seconds for Table2 compared to 246 seconds for Table1. Now I thought when using Load Data Infile on an empty table it would rebuild *all* of the keys AFTER the data has been loaded. This may not be the case. I suspect the extra time for Test1 is caused by the Load Data building the primary key as the data is being loaded. Can someone confirm this? If so, then when loading data into an empty table, it is always going to be faster to remove the keys then load the data, then add the keys. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=john.dais...@butterflysystems.co.uk No virus found in this incoming message. Checked by AVG - www.avg.com Version: 8.5.422 / Virus Database: 270.14.20/2441 - Release Date: 10/16/09 18:39:00 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
