List:General Discussion« Previous MessageNext Message »
From:Daevid Vincent Date:January 3 2011 4:48am
Subject:RE: This just seems to slow
View as plain text  
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.

Also delete your INDEX / KEYs and add them at the very end instead. 

-----Original Message-----
From: mos [mailto:mos99@stripped] 
Sent: Sunday, January 02, 2011 8:42 PM
To: mysql@stripped
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: jerry@stripped
>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=1


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

Thread
This just seems to slowJerry Schwartz3 Jan
  • Re: This just seems to slowmos3 Jan
    • RE: This just seems to slowDaevid Vincent3 Jan
      • Re: This just seems to slowWagner Bianchi3 Jan
        • RE: This just seems to slowJerry Schwartz3 Jan
      • RE: This just seems to slowJerry Schwartz3 Jan
        • Re: This just seems to slowMySQL)3 Jan
    • RE: This just seems to slowJerry Schwartz3 Jan
      • RE: This just seems to slowGavin Towey3 Jan
        • RE: This just seems to slowJerry Schwartz3 Jan
      • RE: This just seems to slowmos3 Jan
        • RE: This just seems to slowJerry Schwartz4 Jan
          • RE: This just seems to slowmos4 Jan
            • RE: This just seems to slowJerry Schwartz4 Jan
  • mysqlimport doesn't work for meJerry Schwartz3 Jan
    • Re: mysqlimport doesn't work for meCarsten Pedersen3 Jan
      • RE: mysqlimport doesn't work for meJerry Schwartz3 Jan