I just loaded about 125,000 records, each containing about 30 columnsFirstly, I'd drop those indexes, and add them back after the import is done ( in a single alter table statement).
into an empty, newly created, mysql table. It took 56 minutes. Isn't
that a little long? The table design was driven, in part, by an existing reporting
application. The data to populate the table is being exported from
Lotus Notes. I'm including the table DDL and the load statement. If anybody sees
anything obvious, please let me know.
CREATE TABLE ln_invoice ( ServerRepID CHAR(16) NOT NULL, DocUNID CHAR(32) NOT NULL, InvoiceNum VARCHAR(25) NOT NULL, FormStatus char(10), Status char(1), Company VARCHAR(60), Regarding VARCHAR(50), CMCompanyID VARCHAR(32), IN_Date date NOT NULL default '0000-00-00', Branch CHAR(10), Contact VARCHAR(100) default '', OriginatingPartner VARCHAR(50) default '', SupervisingPartner VARCHAR(50) default '', Attorney VARCHAR(50) default '', Attorney2 VARCHAR(50) default '', Paralegal VARCHAR(50) default '', Paralegal2 VARCHAR(50) default '', Total decimal(7,2) NOT NULL default '0.00', Outstanding decimal(7,2) NOT NULL default '0.00', Address1 VARCHAR(100) default '', Address2 VARCHAR(100) default '', Address3 VARCHAR(100) default '', City VARCHAR(50) default '', State CHAR(2) default '', Zip VARCHAR(10) default '', Country VARCHAR(20) default '', LglFeeTotal decimal(7,2) NOT NULL default '0.00', Disbursements decimal(7,2) NOT NULL default '0.00', Discount decimal(7,2) NOT NULL default '0.00', Team VARCHAR(20) default '', TimeStamp timestamp NULL, PRIMARY KEY(ServerRepID,DocUNID), UNIQUE UNX_DOCUNID (docUnid), KEY INX_INVOICE (InvoiceNum), KEY INX_COMPANY (Company) ) TYPE=InnoDB;
Load statement:
LOAD DATA LOCAL INFILE 'c:/mysql/masterbilling-Invoices.txt' REPLACE INTO TABLE ln_invoice FIELDS TERMINATED BY '|' ENCLOSED BY '~' LINES TERMINATED BY '\r\n' ;
Secondly, you have a primary key across 2 fields. I'm just guessing here, but I assume that hurts performance, especially when doing bulk inserts.
I'd create an auto_increment field as the primary key, *after* the import. As for the need to keep the (ServerRepID,DocUNID) unique, I'd add a unique index across it, also after the import, in the same step that you add the primary key and other indexes.
Third, you didn't say what type of hardware you're running, so it's difficult whether you have a performance problem or not.
-- Daniel Kasak IT Developer * NUS Consulting Group* Level 18, 168 Walker Street North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
