At 10:34 AM 8/18/2010, Xn Nooby wrote:
minutes to dump the 70M rows. However, it takes the LOAD FILE command
13 hours to import the CSV file. My
understanding of LOAD FILE was that it was already optimized to load
the data, then build the indices afterwords. I
don't understand why it takes so long.
A common misconception about Load Data Infile command is that it will
rebuild the indexes later only if you are loading data into an empty table
(which you probably are) and only for the non-unique indexes. The Unique
and Primary indexes are still being built during the loading of the data
and I suspect this is why it is slowing down over time.
Before loading the data, I would recommend removing ALL of the indexes.
Then after the data has been loaded, issue a single Alter statement to
rebuild all of the indexes in this one command. See if that makes a
difference.
The other alternative is to create another table with the new table
structure but without the indexes. Then do a
insert into newtable select * from oldtable;
and then create the indexes on the new table with a single Alter statement.
Mike
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
