>-----Original Message----- >From: Daevid Vincent [mailto:dae...@daevid.com] >Sent: Sunday, January 02, 2011 11:49 PM >To: mysql@lists.mysql.com >Cc: 'mos' >Subject: RE: This just seems to slow > >Another option would be to mangle your insert statement with some other >language like PHP, Python, Ruby, etc. so that the inserts are MULTI inserts >instead. Something like: > >INSERT INTO `T_DMU_History` (`DM_History_DM_ID`, `DM_History_Customer_ID`) >VALUES (13071, 299519), >VALUES (13071, 299520), >VALUES (13071, 299521), >... > >That will radically speed up the inserts. > [JS] I thought of that, but unfortunately this is just one of 25-odd tables (each in a different format, of course).
>Also delete your INDEX / KEYs and add them at the very end instead. > [JS] Wouldn't it take as long to build the indices? I guess it probably wouldn't. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 E-mail: je...@gii.co.jp Web site: www.the-infoshop.com >-----Original Message----- >From: mos [mailto:mo...@fastmail.fm] >Sent: Sunday, January 02, 2011 8:42 PM >To: mysql@lists.mysql.com >Subject: Re: This just seems to slow > >Jerry, > Use "Load Data Infile" when loading a lot of data. Whoever is giving >you the data should be able to dump it to a CSV file. Your imports will be >much faster. > >Mike > >At 07:51 PM 1/2/2011, you wrote: >>I'm trying to load data into a simple table, and it is taking many hours >(and >>still not done). I know hardware, etc., can have a big effect, but NOTHING >>should have this big an effect. >> >>===== >>us-gii >show create table t_dmu_history\G >>*************************** 1. row *************************** >> Table: t_dmu_history >>Create Table: CREATE TABLE `t_dmu_history` ( >> `t_dmu_history_id` int(11) NOT NULL AUTO_INCREMENT, >> `DM_History_DM_ID` int(11) DEFAULT NULL, >> `DM_History_Customer_ID` int(11) DEFAULT NULL, >> PRIMARY KEY (`t_dmu_history_id`), >> KEY `DM_History_DM_ID` (`DM_History_DM_ID`), >> KEY `DM_History_Customer_ID` (`DM_History_Customer_ID`) >>) ENGINE=InnoDB AUTO_INCREMENT=1446317 DEFAULT CHARSET=utf8 >>===== >> >>Here's a snip of what the input file looks like: >>===== >>SET autocommit=1; >> >># >># Dumping data for table 'T_DMU_History' >># >> >>INSERT INTO `T_DMU_History` (`DM_History_DM_ID`, `DM_History_Customer_ID`) >>VALUES (13071, 299519); >>INSERT INTO `T_DMU_History` (`DM_History_DM_ID`, `DM_History_Customer_ID`) >>VALUES (13071, 299520); >>INSERT INTO `T_DMU_History` (`DM_History_DM_ID`, `DM_History_Customer_ID`) >>VALUES (13071, 299521); >>INSERT INTO `T_DMU_History` (`DM_History_DM_ID`, `DM_History_Customer_ID`) >>VALUES (13071, 299522); >>INSERT INTO `T_DMU_History` (`DM_History_DM_ID`, `DM_History_Customer_ID`) >>VALUES (13071, 299524); >>INSERT INTO `T_DMU_History` (`DM_History_DM_ID`, `DM_History_Customer_ID`) >>VALUES (13071, 299526); >>INSERT INTO `T_DMU_History` (`DM_History_DM_ID`, `DM_History_Customer_ID`) >>VALUES (13071, 299527); >>INSERT INTO `T_DMU_History` (`DM_History_DM_ID`, `DM_History_Customer_ID`) >>VALUES (13071, 299528); >>INSERT INTO `T_DMU_History` (`DM_History_DM_ID`, `DM_History_Customer_ID`) >>VALUES (13071, 299529); >>INSERT INTO `T_DMU_History` (`DM_History_DM_ID`, `DM_History_Customer_ID`) >>VALUES (13071, 299531); >>INSERT INTO `T_DMU_History` (`DM_History_DM_ID`, `DM_History_Customer_ID`) >>VALUES (13071, 299532); >>INSERT INTO `T_DMU_History` (`DM_History_DM_ID`, `DM_History_Customer_ID`) >>VALUES (13071, 299533); >>INSERT INTO `T_DMU_History` (`DM_History_DM_ID`, `DM_History_Customer_ID`) >>VALUES (13071, 299534); >>INSERT INTO `T_DMU_History` (`DM_History_DM_ID`, `DM_History_Customer_ID`) >>VALUES (13071, 299535); >>INSERT INTO `T_DMU_History` (`DM_History_DM_ID`, `DM_History_Customer_ID`) >>VALUES (13073, 298880); >>INSERT INTO `T_DMU_History` (`DM_History_DM_ID`, `DM_History_Customer_ID`) >>VALUES (13073, 298881); >>INSERT INTO `T_DMU_History` (`DM_History_DM_ID`, `DM_History_Customer_ID`) >>VALUES (13073, 298882); >>INSERT INTO `T_DMU_History` (`DM_History_DM_ID`, `DM_History_Customer_ID`) >>VALUES (13073, 298883); >>INSERT INTO `T_DMU_History` (`DM_History_DM_ID`, `DM_History_Customer_ID`) >>VALUES (13073, 298884); >>INSERT INTO `T_DMU_History` (`DM_History_DM_ID`, `DM_History_Customer_ID`) >>VALUES (13073, 298885); >>INSERT INTO `T_DMU_History` (`DM_History_DM_ID`, `DM_History_Customer_ID`) >>VALUES (13073, 298886); >>INSERT INTO `T_DMU_History` (`DM_History_DM_ID`, `DM_History_Customer_ID`) >>VALUES (13073, 298887); >>INSERT INTO `T_DMU_History` (`DM_History_DM_ID`, `DM_History_Customer_ID`) >>VALUES (13073, 298889); >>INSERT INTO `T_DMU_History` (`DM_History_DM_ID`, `DM_History_Customer_ID`) >>VALUES (13073, 298890); >>INSERT INTO `T_DMU_History` (`DM_History_DM_ID`, `DM_History_Customer_ID`) >>VALUES (13073, 298891); >>===== >> >>There are about 870000 records. >> >>I realize that using one INSERT per row is going to hurt, but I don't >control >>the format of the incoming data. >> >>Besides, I'd have thought this would be pretty quick regardless of how >clumsy >>the method was. >> >>Is that "autocommit" a problem? This is a bulk load into an empty table, so >>I'm not worried about ACID. >> >>Any suggestions? >> >> >> >> >> >>Regards, >> >>Jerry Schwartz >>Global Information Incorporated >>195 Farmington Ave. >>Farmington, CT 06032 >> >>860.674.8796 / FAX: 860.674.8341 >>E-mail: je...@gii.co.jp >>Web site: www.the-infoshop.com >> >> >> >> >> >>-- >>MySQL General Mailing List >>For list archives: http://lists.mysql.com/mysql >>To unsubscribe: http://lists.mysql.com/mysql?unsub=mo...@fastmail.fm > > >-- >MySQL General Mailing List >For list archives: http://lists.mysql.com/mysql >To unsubscribe: http://lists.mysql.com/mysql?unsub=dae...@daevid.com > > >-- >MySQL General Mailing List >For list archives: http://lists.mysql.com/mysql >To unsubscribe: http://lists.mysql.com/mysql?unsub=je...@gii.co.jp -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
