List:General Discussion« Previous MessageNext Message »
From:mos Date:August 18 2010 4:57pm
Subject:Re: Slow ALTER TABLE on 70M row InnoDB table
View as plain text  
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 

Thread
Slow ALTER TABLE on 70M row InnoDB tableXn Nooby18 Aug
  • Re: Slow ALTER TABLE on 70M row InnoDB tablemos18 Aug
    • Re: Slow ALTER TABLE on 70M row InnoDB tableXn Nooby18 Aug
      • Re: Slow ALTER TABLE on 70M row InnoDB tablemos18 Aug
        • Re: Slow ALTER TABLE on 70M row InnoDB tableXn Nooby18 Aug
        • Re: Slow ALTER TABLE on 70M row InnoDB tableXn Nooby18 Aug
        • Re: Slow ALTER TABLE on 70M row InnoDB tableXn Nooby18 Aug
          • Re: Slow ALTER TABLE on 70M row InnoDB tableEric Bergen21 Aug
  • RE: Slow ALTER TABLE on 70M row InnoDB tableTravis Ard18 Aug