List:General Discussion« Previous MessageNext Message »
From:mos Date:October 19 2009 3:11pm
Subject:RE: Load Data Infile quirk
View as plain text  
At 05:40 AM 10/18/2009, John wrote:
>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.

Ok, I thought that ALL indexes would be rebuilt later, including my primary 
index, and one unique index I have on the table. I must have misread that 
in the manual. Thanks.

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

But Disable Keys has no affect on primary or unique indexes. So the only 
way for me to speed this up on loading data into empty tables is to remove 
all indexes and build them after the data has been loaded. That should save 
me 30% on the load times.

Mike



>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 john@stripped
>
>-----Original Message-----
>From: mos [mailto:mos99@stripped]
>Sent: 17 October 2009 22:49
>To: mysql@stripped
>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=1
>
>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=1

Thread
Load Data Infile quirkmos17 Oct
  • RE: Load Data Infile quirkJohn18 Oct
    • RE: Load Data Infile quirkmos19 Oct