May be a clue, for the data records you load into the table, the value for numeric field such as DEPARTMENT_ID, LOCATION_ID should not be enclosed in quote. If it is the case, mysql has to make a translation from character to numeric.
Marc. -----Message d'origine----- De : [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Envoyé : mercredi 20 avril 2005 11:59 À : mysql@lists.mysql.com Cc : [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED] Objet : Regarding the loading of data usning load data infile Hi, I had 99,990 records to be loaded into a table which is having unique constraints and foreign-key constraints as below CREATE TABLE `teldir` ( `NAME` varchar(21) default '', `PHONE_NO` varchar(26) default '', `PRIME_VALUE` char(1) default '', `COMMENT_TEXT1` varchar(30) default '', `COMMENT_TEXT2` varchar(30) default '', `DEPARTMENT_ID` int(4) default NULL, `LOCATION_ID` int(4) default NULL, `COMPONENT_ID` int(3) default NULL, `NAME_AND_PHONE_NO` varchar(48) NOT NULL default '', `HI_CASE_IND` int(11) default NULL, `LOW_CASE_IND` int(11) default NULL, `PRIVACY` char(1) default '', `COLLECTED` char(1) default '', `HOMENODE_ID` int(3) NOT NULL default '0', `CLUSTERID` int(3) NOT NULL default '0', `PLID_CABINET` int(3) default NULL, `PLID_SHELF` int(3) default NULL, `PLID_SLOT` int(3) default NULL, `PLID_CIRCUIT` int(3) default NULL, `DEVICE_TYPE` int(2) default NULL, `IDS_ID` varchar(255) default '', `ISIDSMANAGED` char(1) default '', `MACADDRESS` varchar(12) default '', `CESID` varchar(10) default '', `hvgPIN` varchar(8) default '', `tdUID` varchar(38) default '', PRIMARY KEY (`NAME_AND_PHONE_NO`), UNIQUE KEY `TD_PHONE_KEY_IDX` (`PHONE_NO`,`NAME_AND_PHONE_NO`), UNIQUE KEY `TD_KEYS_IDX` (`NAME_AND_PHONE_NO`,`NAME`,`PHONE_NO`), UNIQUE KEY `TD_COMP_KEY_IDX` (`COMPONENT_ID`,`NAME_AND_PHONE_NO`), KEY `COMPONENT_ID` (`COMPONENT_ID`), KEY `HOMENODE_ID` (`HOMENODE_ID`), KEY `TD_COMP_PLID_IDX` (`COMPONENT_ID`,`PLID_CABINET`,`PLID_SHELF`,`PLID_SLOT`,`PLID_CIRCUIT`), KEY `TD_IDSID_IDX` (`IDS_ID`), KEY `TD_IDSUNMGT_IDX` (`ISIDSMANAGED`), CONSTRAINT `FK_TELDIR_COMPONENT` FOREIGN KEY (`COMPONENT_ID`) REFERENCES `component` (`ID`), CONSTRAINT `FK_TELDIR_HOMENODE` FOREIGN KEY (`HOMENODE_ID`) REFERENCES `component` (`ID`) ) TYPE=InnoDB Used "load data infile 'teldir.lst' into table teldir". But teldir.lst contains data of 99,990 records (whose fields are separated by tab and rows are separated by newline). To load these many records into teldir table whose structure as above taking around 100 minutes. I.e taking too much of time. If I drop the unique and foreign key constraints it is taking around 25 minutes, which is also large time. Please advise me for a better solution so that the loading of data should be faster. According to the load data standards for innodb it should load 2000 records for second. Please explain me the proper solution for this. Thanks, Narasimha Confidentiality Notice The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain confidential or privileged information. If you are not the intended recipient, please notify the sender at Wipro or [EMAIL PROTECTED] immediately and destroy all copies of this message and any attachments. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
