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 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=mo...@fastmail.fm
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
