Re: Major Difference in response times when using Load Infile utility

[Brent Baisley]

Indexing can play a big role in the load time differential. When  
loading .45 million rows, MySQL may be able to handle the sorting for  
indexes in memory. With 5.5 million, it's more likely to have to go  
to a temp file. You then could end up thrashing you disk, causing big  
slow downs. If you think about it, you'll be reading the import file  
from disk, MySQL will be writing to disk as it loads the database,  
and then the indexing will be reading and writing to disk for  
sorting. That's a lot of activity.

Disable or remove your indexes when doing a huge import, then enable  
or add them when you're done.


On Sep 13, 2005, at 7:24 AM, Sujay Koduri wrote:

> hi ,
>
> I am using the Load Infile utility to load data from file to MySQL DB.
> When trying to load different amounts of data, I observed a notable
> difference in the time taken by that.
>
> Test 1
>
> Amount of data - 5.5 million rows. Time Taken - 6+hrs Approximately.
>
> Test2
>
> Amount of data - 0.45 million rows. Time Taken - 2 mins approximately.
>
> Can some one explain why this difference is coming. Also it will be  
> great if
> someone can suggest how we can improve the performance of the first  
> test.
>
> Thank you
> sujay
>

-- 
Brent Baisley
Systems Architect
Landover Associates, Inc.
Search & Advisory Services for Advanced Technology Environments
p: 212.759.6400/800.759.0577