I think you will find the load data infile will work faster. I am performing testing right now in preparation for a migration from 4.1 to 5.0 but I am confident that will be the case.
Keith ----- Original Message ----- From: "Sid Lane" < [EMAIL PROTECTED] > To: mysql@lists.mysql.com Sent: Monday, July 23, 2007 1:44:53 PM (GMT-0500) America/New_York Subject: performance of extended insert vs. load data all, I need to migrate ~12GB of data from an Oracle 10 database to a MySQL 5.0one in as short a window as practically possible (throw tablespace in r/o, migrate data & repoint web servers - every minute counts). the two approaches I am considering are: 1. write a program that outputs the Oracle data to a fifo pipe (mknod) and running a "load data infile" against it 2. write a program that dynamically builds extended insert statements up to length of max_allowed_packet (similar to mysqldump -e) is either one significantly faster than the other? I know I could benchmark it but I was hoping someone could save me writing #2 to find out if it's not the way to go... are there additional (faster) approaches I have not thought of? FWIW these are 95% innodb (5% myisam are static reference tables & can be done in advance). thanks! -- B. Keith Murphy Database Administrator iContact 2635 Meridian Parkway, 2nd Floor Durham, North Carolina 27713 (o) 919-433-0786 (c) 850-637-3877
