From: Date: September 13 2005 5:43pm
Subject: RE: Major Difference in response times when using Load Infile uti
	lity
List-Archive: http://lists.mysql.com/mysql/189059
Message-Id: <8C004E20A28812439C0E651674CC71230126ACE5@blrmailserv.BANGALORE.COM>
MIME-Version: 1.0
Content-Type: text/plain


Hi

I am INNODB tables only. (I have only one big table) 
Autocommit I have already disabled.
And it doesn't have any foreign keys in it. Do I still need to set the
FOREIGN_KEY_CHECKS to '0'??

sujay
-----Original Message-----
From: Alan Williamson [mailto:teamspike@stripped] 
Sent: Tuesday, September 13, 2005 8:48 PM
To: mysql@stripped
Subject: RE: Major Difference in response times when using Load Infile
utility

> 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.

Is this an InnoDB database by any chance?  If it is, and it is a clean
import, then disable the FOREIGN_KEY_CHECKS.  

  SET AUTOCOMMIT = 0;
  SET FOREIGN_KEY_CHECKS=0;

This is a small tip i picked up on the MySQL documentation that someone had
left in the comments and has been to date one of those tips that has
literally saved DAYS of my life.

a

ps Remember to put them back on again after you finish the import

  SET AUTOCOMMIT = 1;
  SET FOREIGN_KEY_CHECKS=1;

--
 Alan Williamson, Technology Evangelist
 SpikeSource Inc.
 Daily OS News @ http://compiledby.spikesource.com/

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=sujayk@stripped