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=gcdmg-mysql-2@m.gmane.org

RE: Load Data Infile quirk

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 john@butterflysystems.co.uk

-----Original Message-----
From: mos [mailto:mos99@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.daisley@butterflysys tems.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=gcdmg-mysql-2@m.gmane.org

RE: Load Data Infile quirk

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@butterflysystems.co.uk
>
>-----Original Message-----
>From: mos [mailto:mos99@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.daisley@butterflysy stems.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=mos99@fastmail.fm


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org