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]
