Regarding the loading of data usning load data infile

[<lakshmi.narasimharao]

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

-----Original Message-----
From: Heikki Tuuri [mailto:Heikki.Tuuri@stripped]
Sent: Thursday, April 07, 2005 2:52 PM
To: mysql@stripped
Subject: Re: innodb, log_bin and ib_logfiles

Johanne,

----- Original Message -----
From: ""Duhaime Johanne"" <Johanne.Duhaime@stripped>
Newsgroups: mailing.database.myodbc
Sent: Wednesday, April 06, 2005 10:12 PM
Subject: innodb, log_bin and ib_logfiles


> ------_=_NextPart_001_01C53ADC.86591B2F
> Content-Type: text/plain;
> charset="us-ascii"
> Content-Transfer-Encoding: quoted-printable
>
> I have looked  around in the documentation but  I do  not have a clear
> idea of log_bin vs ib_lofile for innodb.
> =20
> Regarding only INNODB tables, am I right if I say that:=20
> =20
> log_bin are the commit transactions

yes. The binlog is used to roll-forward from a backup.

> and it is what is use in an
> automatic recovery or are to be apply in a manual recovery from a dump
> (In Oracle they are the Redo.logs)  .=20
> ib_logfiles are storing the uncommit and commit transactiond and
allows
> the rollback of transactions

No. ib_logfiles are a page-level 'physiological' redo log. Used in crash

recovery.

> (In Oracle they are the Rollback segments)?

Rollback segments in InnoDB are called 'undo logs'. They are in the
ibdata
files. They are used in rollback, and in 'consistent read' SELECT.

> They are not use in the automatic recovery of innodb nor in a manual
> recovery from a dump.
> =20
> I would appreciate very much if someone can clarify this for me.
> =20
> =20
> Johanne Duhaime

Best regards,

Heikki Tuuri
Innobase Oy
Foreign keys, transactions, and row level locking for MySQL
InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up
MyISAM
tables
http://www.innodb.com/order.php


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




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
Mailadmin@stripped immediately
and destroy all copies of this message and any attachments.