RE: Major Difference in response times when using Load Infile uti lity

[Sujay Koduri]

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=1