MySQL csv import incremental slowdown (alternatives?)

I have the following table:

CREATE  TABLE `Product_Category_Rank` (
  `CategoryId` SMALLINT(5) UNSIGNED NOT NULL ,
  `ProductId` VA开发者_Go百科RCHAR(32) NOT NULL ,
  `RankedOn` DATE NOT NULL ,
  `PopularityRank` SMALLINT(5) NOT NULL ,
  PRIMARY KEY (`CategoryId`, `ProductId`, `RankedOn`) ,
  INDEX `fk_Product_Category_Rank_Product` (`ProductId` ASC) ,
  INDEX `fk_Product_Category_Rank_Category` (`CategoryId` ASC) ,
  CONSTRAINT `fk_Product_Category_Rank_Category`
    FOREIGN KEY (`CategoryId` )
    REFERENCES `Category` (`CategoryId` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Product_Category_Rank_Product`
    FOREIGN KEY (`ProductId` )
    REFERENCES `Product` (`ProductId` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci

I have a csv file (which I am importing daily) that contains just under 30,000 records.

I have tried:

LOAD DATA LOCAL INFILE 'temp/product_category_rank.csv'
INTO TABLE `Product_Category_Rank`
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"'
LINES TERMINATED BY '\n';

As well I have tried loading the file into a temporary table and doing:

INSERT
  INTO `Product_Category_Rank` ( ... )
SELECT ...
  FROM `tmp_product_category_rank`

Both methods work equally (same results), however the second I am sure has a slightly higher resource cost. The problem I am running into is that every day the import time on the load / insert-select functionality is increasing by about 3 seconds / day. So day 30 I am looking at 90 seconds to do a straight import into the table.

Facts: the import will never have duplicate primary keys (unless of course I try to import the same data twice, which is not an issue)

So is there a way to speed up this process (maybe turning off key checking before the import? (how?))

edit: Also to note that the foreign key checks are not required either as the Category and Product tables have already been updated and the foreign key constraints are not an issue with the import.

---

Solution: (Apparently had nothing to do with the query)

MySQL server variables:

innodb_buffer_pool_size = 8MB (changed to 2GB) innodb_log_file_size = 5MB (changed to 256MB) innodb_log_buffer_size = 1MB (changed to 4MB) innodb_flush_log_at_trx_commit = 1 (changed to 2)

These new settings are based on an article found at : http://www.mysqlperformanceblog.com/2007/11/01/innodb-performance-optimization-basics/

Now as I run the import, there is no degradation to the speed of the imports on a day to day basis. I have imported 30 days and each import is the same speed as the last. So it was actually a server optimization issue.