mysql optimization

I need to optimize a MySQL database insert process that currently takes a long time to complete. Due to bad experiences on RAC with coders who do not understand anything about MySQL, I will provide the project details only to coders who have the qualifications to work on the project. IN ORDER TO BID ON THIS PROJECT: You need to have Linux locally installed, with MySQL Server version: 5.0.22-Debian_0ubuntu6.06.10-log. Once you confirm that you have the above or the ability to install the above locally in your own environment, I will post the project details via RAC private message. ## Deliverables 1) Complete and fully-functional working program(s) in executable form as well as complete source code of all work done. 2) Deliverables must be in ready-to-run condition, as follows? (depending on the nature? of the deliverables): a)? For web sites or? other server-side deliverables intended to only ever exist in one place in the Buyer's environment--Deliverables must be installed by the Seller in ready-to-run condition in the Buyer's environment. b) For all others including desktop software or software the buyer intends to distribute: A software? installation package that will install the software in ready-to-run condition on the platform(s) specified in this bid request. 3) All deliverables will be considered "work made for hire" under U.S. Copyright law. Buyer will receive exclusive and complete copyrights to all work purchased. (No GPL, GNU, 3rd party components, etc. unless all copyright ramifications are explained AND AGREED TO by the buyer on the site per the coder's Seller Legal Agreement). * * *This broadcast message was sent to all bidders on Saturday Aug 30, 2008 4:52:53 PM: Here is some additional details. The main problem is a LOAD DATA INFILE query which dumps into a table that has triggers. I'd like this query to run faster by a factor of 2x to 3x. (NOTE, this query is modified for benchmark purposes and does not replicate the EXACT business scenario) delete from idx_datafeed_file_commit where IMPORT_FILE_DATE >= '2008-08-21'; drop table if exists idx_datafeed_Plus_Residential_TMP; create table IF NOT EXISTS idx_datafeed_Plus_Residential_HIST ( IMPORT_ROW_ID INT(10) UNSIGNED ZEROFILL NOT NULL, IMPORT_FILE_DATE DATE NOT NULL, LN DECIMAL(8,0) UNSIGNED ZEROFILL NOT NULL, PRIMARY KEY(LN,IMPORT_FILE_DATE), INDEX(IMPORT_FILE_DATE), INDEX(IMPORT_ROW_ID) ) /*PARTITION BY HASH(DAYOFMONTH(IMPORT_FILE_DATE)) PARTITIONS 31*/; create table IF NOT EXISTS idx_datafeed_Plus_Residential_CURR ( IMPORT_ROW_ID INT(10) UNSIGNED ZEROFILL NOT NULL, IMPORT_FILE_DATE DATE NOT NULL, LN DECIMAL(8,0) UNSIGNED ZEROFILL NOT NULL, PRIMARY KEY(LN), INDEX(IMPORT_ROW_ID) ) /*PARTITION BY HASH(DAYOFMONTH(IMPORT_FILE_DATE)) PARTITIONS 31*/; create table IF NOT EXISTS idx_datafeed_Plus_Residential_DATA ( `LN` TINYTEXT NOT NULL, `TYP` TINYTEXT NOT NULL, `UD` TINYTEXT NOT NULL, `LAG` TINYTEXT NOT NULL, `LO` TI NYTEXT NOT NULL, `OFFICE_PHONE` TINYTEXT NOT NULL, `AGENT_FORM_NAME` TINYTEXT NOT NULL, `OFFICE_FORM_NAME` TINYTE XT NOT NULL, `LP` TINYTEXT NOT NULL, `HSN` TINYTEXT NOT NULL, `CP` TINYTEXT NOT NULL, `STR` TINYTEXT NOT NULL, `U N` TINYTEXT NOT NULL, `CIT` TINYTEXT NOT NULL, `ADI` TINYTEXT NOT NULL, `AR` TINYTEXT NOT NULL, `AREA_Name` TINYT EXT NOT NULL, `SUB` TINYTEXT NOT NULL, `CNY` TINYTEXT NOT NULL, `RMS` TINYTEXT NOT NULL, `BR` TINYTEXT NOT NULL, `BTH` TINYTEXT NOT NULL, `MBB` TINYTEXT NOT NULL, `CARS` TINYTEXT NOT NULL, `GAR` TINYTEXT NOT NULL, `PKN` TINYTE XT NOT NULL, `PAR` TINYTEXT NOT NULL, `FP` TINYTEXT NOT NULL, `BLT` TINYTEXT NOT NULL, `LSZ` TINYTEXT NOT NULL, ` ACR` TINYTEXT NOT NULL, `WF` TINYTEXT NOT NULL, `BMT` TINYTEXT NOT NULL, `MOD` TINYTEXT NOT NULL, `TAX` TINYTEXT NOT NULL, `TXY` TINYTEXT NOT NULL, `ASM` TINYTEXT NOT NULL, `MBS` TINYTEXT NOT NULL, `MBF` TINYTEXT NOT NULL, `B2 S` TINYTEXT NOT NULL, `B2F` TINYTEXT NOT NULL, `B3S` TINYTEXT NOT NULL, `B3F` TINYTEXT NOT NULL, `B4S` TINYTEXT N OT NULL, `B4F` TINYTEXT NOT NULL, `LRS` TINYTEXT NOT NULL, `LRF` TINYTEXT NOT NULL, `DRS` TINYTEXT NOT NULL, `DRF ` TINYTEXT NOT NULL, `KTS` TINYTEXT NOT NULL, `KTF` TINYTEXT NOT NULL, `FRS` TINYTEXT NOT NULL, `FRF` TINYTEXT NO T NULL, `RE1` TINYTEXT NOT NULL, `RE2` TINYTEXT NOT NULL, `RE3` TINYTEXT NOT NULL, `RE4` TINYTEXT NOT NULL, `RE5` TINYTEXT NOT NULL, `RE6` TINYTEXT NOT NULL, `RE7` TINYTEXT NOT NULL, `RE8` TINYTEXT NOT NULL, `RE9` TINYTEXT NOT NULL, `RE10` TINYTEXT NOT NULL, `A1N` TINYTEXT NOT NULL, `A1S` TINYTEXT NOT NULL, `A1F` TINYTEXT NOT NULL, `A2N` TINYTEXT NOT NULL, `A2S` TINYTEXT NOT NULL, `A2F` TINYTEXT NOT NULL, `A3N` TINYTEXT NOT NULL, `A3S` TINYTEXT NOT NULL, `A3F` TINYTEXT NOT NULL, `A4N` TINYTEXT NOT NULL, `A4S` TINYTEXT NOT NULL, `A4F` TINYTEXT NOT NULL, `A5N` TINYTEXT NOT NULL, `A5S` TINYTEXT NOT NULL, `A5F` TINYTEXT NOT NULL, `GS` TINYTEXT NOT NULL, `GSD` TINYTEXT NOT N ULL, `JH` TINYTEXT NOT NULL, `JHD` TINYTEXT NOT NULL, `HS` TINYTEXT NOT NULL, `HSD` TINYTEXT NOT NULL, `OT` TINYT EXT NOT NULL, `OSD` TINYTEXT NOT NULL, `FEA` TINYTEXT NOT NULL, `OTR` TINYTEXT NOT NULL, `STY` TINYTEXT NOT NULL, `TPE` TINYTEXT NOT NULL, `TPC` TINYTEXT NOT NULL, `TMU` TINYTEXT NOT NULL, `TMF` TINYTEXT NOT NULL, `DIR` TINYTE XT NOT NULL, `DR1` TINYTEXT NOT NULL, `DR2` TINYTEXT NOT NULL, `DR3` TINYTEXT NOT NULL, `UFL` TINYTEXT NOT NULL, `FLN` TINYTEXT NOT NULL, `ASF` TINYTEXT NOT NULL, `PTA` TINYTEXT NOT NULL, `CAA` TINYTEXT NOT NULL, `EXP` TINYTEX T NOT NULL, `UFE` TINYTEXT NOT NULL, `TNU` TINYTEXT NOT NULL, `UF1` TINYTEXT NOT NULL, `RM1` TINYTEXT NOT NULL, ` BR1` TINYTEXT NOT NULL, `BT1` TINYTEXT NOT NULL, `UF2` TINYTEXT NOT NULL, `RM2` TINYTEXT NOT NULL, `BR2` TINYTEXT NOT NULL, `BT2` TINYTEXT NOT NULL, `UF3` TINYTEXT NOT NULL, `RM3` TINYTEXT NOT NULL, `BR3` TINYTEXT NOT NULL, `B T3` TINYTEXT NOT NULL, `UF4` TINYTEXT NOT NULL, `RM4` TINYTEXT NOT NULL, `BR4` TINYTEXT NOT NULL, `BT4` TINYTEXT NOT NULL, `GRI` TINYTEXT NOT NULL, `NOI` TINYTEXT NOT NULL, `AP1` TINYTEXT NOT NULL, `AP2` TINYTEXT NOT NULL, `AP 3` TINYTEXT NOT NULL, `AP4` TINYTEXT NOT NULL, `AZN` TINYTEXT NOT NULL, `SLN` TINYTEXT NOT NULL, `ASQ` TINYTEXT N OT NULL, `TLA` TINYTEXT NOT NULL, `RU` TINYTEXT NOT NULL, `LT` TINYTEXT NOT NULL, `AML` TINYTEXT NOT NULL, `BIM` TINYTEXT NOT NULL, `CUU` TINYTEXT NOT NULL, `FMT` TINYTEXT NOT NULL, `LND` TINYTEXT NOT NULL, `LOCAT` TINYTEXT NO T NULL, `PTU` TINYTEXT NOT NULL, `GSI` TINYTEXT NOT NULL, `TAE` TINYTEXT NOT NULL, `GSA` TINYTEXT NOT NULL, `TO` TINYTEXT NOT NULL, `UNT` TINYTEXT NOT NULL, `SUBTPE` TINYTEXT NOT NULL, `RP` TINYTEXT NOT NULL, `MIN` TINYTEXT NO T NULL, `MAX` TINYTEXT NOT NULL, `BSQ` TINYTEXT NOT NULL, `BAG` TINYTEXT NOT NULL, `BUT` TINYTEXT NOT NULL, `APT` TINYTEXT NOT NULL, `OFC` TINYTEXT NOT NULL, `STO` TINYTEXT NOT NULL, `DID` TINYTEXT NOT NULL, `NDK` TINYTEXT NOT NULL, `MXF` TINYTEXT NOT NULL, `MXI` TINYTEXT NOT NULL, `MIF` TINYTEXT NOT NULL, `MII` TINYTEXT NOT NULL, `HT` T INYTEXT NOT NULL, `GD` TINYTEXT NOT NULL, `Park` TINYTEXT NOT NULL, `DBL` TINYTEXT NOT NULL, `MK` TINYTEXT NOT NU LL, `NBR_PHOTOS` TINYTEXT NOT NULL, `SUPP_PHOTOS` TINYTEXT NOT NULL, `ZP` TINYTEXT NOT NULL, `Tour_URL` TINYTEXT NOT NULL, `ST` TINYTEXT NOT NULL, `AGE` TINYTEXT NOT NULL, IMPORT_HASH BINARY(20) NOT NULL, IMPORT_ROW_ID INT(10) UNSIGNED ZEROFILL AUTO_INCREMENT NOT NULL, IMPORT_FIRST_DATE DATE NOT NULL, IMPORT_LAST_DATE DATE NOT NULL, PRIMARY KEY(IMPORT_ROW_ID), UNIQUE(IMPORT_HASH), INDEX(IMPORT_FIRST_DATE), INDEX(IMPORT_LAST_DATE), INDEX(IMPORT_HASH,IMPORT_ROW_ID) ) /*PARTITION BY HASH(DAYOFMONTH(IMPORT_FIRST_DATE)) PARTITIONS 31*/; delete from idx_datafeed_Plus_Residential_HIST where IMPORT_FILE_DATE >= '2008-08-21'; delete from idx_datafeed_Plus_Residential_CURR where IMPORT_FILE_DATE >= '2008-08-21'; delete from idx_datafeed_Plus_Residential_DATA where IMPORT_FIRST_DATE >= '2008-08-21'; create table IF NOT EXISTS idx_datafeed_Plus_Residential_TMP ( `LN` TINYTEXT NOT NULL, `TYP` TINYTEXT NOT NULL, `UD` TINYTEXT NOT NULL, `LAG` TINYTEXT NOT NULL, `LO` TI NYTEXT NOT NULL, `OFFICE_PHONE` TINYTEXT NOT NULL, `AGENT_FORM_NAME` TINYTEXT NOT NULL, `OFFICE_FORM_NAME` TINYTE XT NOT NULL, `LP` TINYTEXT NOT NULL, `HSN` TINYTEXT NOT NULL, `CP` TINYTEXT NOT NULL, `STR` TINYTEXT NOT NULL, `U N` TINYTEXT NOT NULL, `CIT` TINYTEXT NOT NULL, `ADI` TINYTEXT NOT NULL, `AR` TINYTEXT NOT NULL, `AREA_Name` TINYT EXT NOT NULL, `SUB` TINYTEXT NOT NULL, `CNY` TINYTEXT NOT NULL, `RMS` TINYTEXT NOT NULL, `BR` TINYTEXT NOT NULL, `BTH` TINYTEXT NOT NULL, `MBB` TINYTEXT NOT NULL, `CARS` TINYTEXT NOT NULL, `GAR` TINYTEXT NOT NULL, `PKN` TINYTE XT NOT NULL, `PAR` TINYTEXT NOT NULL, `FP` TINYTEXT NOT NULL, `BLT` TINYTEXT NOT NULL, `LSZ` TINYTEXT NOT NULL, ` ACR` TINYTEXT NOT NULL, `WF` TINYTEXT NOT NULL, `BMT` TINYTEXT NOT NULL, `MOD` TINYTEXT NOT NULL, `TAX` TINYTEXT NOT NULL, `TXY` TINYTEXT NOT NULL, `ASM` TINYTEXT NOT NULL, `MBS` TINYTEXT NOT NULL, `MBF` TINYTEXT NOT NULL, `B2 S` TINYTEXT NOT NULL, `B2F` TINYTEXT NOT NULL, `B3S` TINYTEXT NOT NULL, `B3F` TINYTEXT NOT NULL, `B4S` TINYTEXT N OT NULL, `B4F` TINYTEXT NOT NULL, `LRS` TINYTEXT NOT NULL, `LRF` TINYTEXT NOT NULL, `DRS` TINYTEXT NOT NULL, `DRF ` TINYTEXT NOT NULL, `KTS` TINYTEXT NOT NULL, `KTF` TINYTEXT NOT NULL, `FRS` TINYTEXT NOT NULL, `FRF` TINYTEXT NO T NULL, `RE1` TINYTEXT NOT NULL, `RE2` TINYTEXT NOT NULL, `RE3` TINYTEXT NOT NULL, `RE4` TINYTEXT NOT NULL, `RE5` TINYTEXT NOT NULL, `RE6` TINYTEXT NOT NULL, `RE7` TINYTEXT NOT NULL, `RE8` TINYTEXT NOT NULL, `RE9` TINYTEXT NOT NULL, `RE10` TINYTEXT NOT NULL, `A1N` TINYTEXT NOT NULL, `A1S` TINYTEXT NOT NULL, `A1F` TINYTEXT NOT NULL, `A2N` TINYTEXT NOT NULL, `A2S` TINYTEXT NOT NULL, `A2F` TINYTEXT NOT NULL, `A3N` TINYTEXT NOT NULL, `A3S` TINYTEXT NOT NULL, `A3F` TINYTEXT NOT NULL, `A4N` TINYTEXT NOT NULL, `A4S` TINYTEXT NOT NULL, `A4F` TINYTEXT NOT NULL, `A5N` TINYTEXT NOT NULL, `A5S` TINYTEXT NOT NULL, `A5F` TINYTEXT NOT NULL, `GS` TINYTEXT NOT NULL, `GSD` TINYTEXT NOT N ULL, `JH` TINYTEXT NOT NULL, `JHD` TINYTEXT NOT NULL, `HS` TINYTEXT NOT NULL, `HSD` TINYTEXT NOT NULL, `OT` TINYT EXT NOT NULL, `OSD` TINYTEXT NOT NULL, `FEA` TINYTEXT NOT NULL, `OTR` TINYTEXT NOT NULL, `STY` TINYTEXT NOT NULL, `TPE` TINYTEXT NOT NULL, `TPC` TINYTEXT NOT NULL, `TMU` TINYTEXT NOT NULL, `TMF` TINYTEXT NOT NULL, `DIR` TINYTE XT NOT NULL, `DR1` TINYTEXT NOT NULL, `DR2` TINYTEXT NOT NULL, `DR3` TINYTEXT NOT NULL, `UFL` TINYTEXT NOT NULL, `FLN` TINYTEXT NOT NULL, `ASF` TINYTEXT NOT NULL, `PTA` TINYTEXT NOT NULL, `CAA` TINYTEXT NOT NULL, `EXP` TINYTEX T NOT NULL, `UFE` TINYTEXT NOT NULL, `TNU` TINYTEXT NOT NULL, `UF1` TINYTEXT NOT NULL, `RM1` TINYTEXT NOT NULL, ` BR1` TINYTEXT NOT NULL, `BT1` TINYTEXT NOT NULL, `UF2` TINYTEXT NOT NULL, `RM2` TINYTEXT NOT NULL, `BR2` TINYTEXT NOT NULL, `BT2` TINYTEXT NOT NULL, `UF3` TINYTEXT NOT NULL, `RM3` TINYTEXT NOT NULL, `BR3` TINYTEXT NOT NULL, `B T3` TINYTEXT NOT NULL, `UF4` TINYTEXT NOT NULL, `RM4` TINYTEXT NOT NULL, `BR4` TINYTEXT NOT NULL, `BT4` TINYTEXT NOT NULL, `GRI` TINYTEXT NOT NULL, `NOI` TINYTEXT NOT NULL, `AP1` TINYTEXT NOT NULL, `AP2` TINYTEXT NOT NULL, `AP 3` TINYTEXT NOT NULL, `AP4` TINYTEXT NOT NULL, `AZN` TINYTEXT NOT NULL, `SLN` TINYTEXT NOT NULL, `ASQ` TINYTEXT N OT NULL, `TLA` TINYTEXT NOT NULL, `RU` TINYTEXT NOT NULL, `LT` TINYTEXT NOT NULL, `AML` TINYTEXT NOT NULL, `BIM` TINYTEXT NOT NULL, `CUU` TINYTEXT NOT NULL, `FMT` TINYTEXT NOT NULL, `LND` TINYTEXT NOT NULL, `LOCAT` TINYTEXT NO T NULL, `PTU` TINYTEXT NOT NULL, `GSI` TINYTEXT NOT NULL, `TAE` TINYTEXT NOT NULL, `GSA` TINYTEXT NOT NULL, `TO` TINYTEXT NOT NULL, `UNT` TINYTEXT NOT NULL, `SUBTPE` TINYTEXT NOT NULL, `RP` TINYTEXT NOT NULL, `MIN` TINYTEXT NO T NULL, `MAX` TINYTEXT NOT NULL, `BSQ` TINYTEXT NOT NULL, `BAG` TINYTEXT NOT NULL, `BUT` TINYTEXT NOT NULL, `APT` TINYTEXT NOT NULL, `OFC` TINYTEXT NOT NULL, `STO` TINYTEXT NOT NULL, `DID` TINYTEXT NOT NULL, `NDK` TINYTEXT NOT NULL, `MXF` TINYTEXT NOT NULL, `MXI` TINYTEXT NOT NULL, `MIF` TINYTEXT NOT NULL, `MII` TINYTEXT NOT NULL, `HT` T INYTEXT NOT NULL, `GD` TINYTEXT NOT NULL, `Park` TINYTEXT NOT NULL, `DBL` TINYTEXT NOT NULL, `MK` TINYTEXT NOT NU LL, `NBR_PHOTOS` TINYTEXT NOT NULL, `SUPP_PHOTOS` TINYTEXT NOT NULL, `ZP` TINYTEXT NOT NULL, `Tour_URL` TINYTEXT NOT NULL, `ST` TINYTEXT NOT NULL, `AGE` TINYTEXT NOT NULL, IMPORT_HASH BINARY(20) ); drop table if exists idx_datafeed_Resi_Expanded_TMP; create table IF NOT EXISTS idx_datafeed_Resi_Expanded_HIST ( IMPORT_ROW_ID INT(10) UNSIGNED ZEROFILL NOT NULL, IMPORT_FILE_DATE DATE NOT NULL, LN DECIMAL(8,0) UNSIGNED ZEROFILL NOT NULL, PRIMARY KEY(LN,IMPORT_FILE_DATE), INDEX(IMPORT_FILE_DATE), INDEX(IMPORT_ROW_ID) ) /*PARTITION BY HASH(DAYOFMONTH(IMPORT_FILE_DATE)) PARTITIONS 31*/; create table IF NOT EXISTS idx_datafeed_Resi_Expanded_CURR ( IMPORT_ROW_ID INT(10) UNSIGNED ZEROFILL NOT NULL, IMPORT_FILE_DATE DATE NOT NULL, LN DECIMAL(8,0) UNSIGNED ZEROFILL NOT NULL, PRIMARY KEY(LN), INDEX(IMPORT_ROW_ID) ) /*PARTITION BY HASH(DAYOFMONTH(IMPORT_FILE_DATE)) PARTITIONS 31*/; create table IF NOT EXISTS idx_datafeed_Resi_Expanded_DATA ( `LN` TINYTEXT NOT NULL, `TYP` TINYTEXT NOT NULL, `COLISTER` TINYTEXT NOT NULL, `CLI` TINYTEXT NOT NULL, ` CRP` TINYTEXT NOT NULL, `GS` TINYTEXT NOT NULL, `HS` TINYTEXT NOT NULL, `JH` TINYTEXT NOT NULL, `PIN` TINYTEXT NO T NULL, `STR` TINYTEXT NOT NULL, `TOUR_URL` TINYTEXT NOT NULL, `TWN` TINYTEXT NOT NULL, `TXY` TINYTEXT NOT NULL, `STATE` TINYTEXT NOT NULL, `LAT` TINYTEXT NOT NULL, `LNG` TINYTEXT NOT NULL, `REMARKS` TINYTEXT NOT NULL, IMPORT_HASH BINARY(20) NOT NULL, IMPORT_ROW_ID INT(10) UNSIGNED ZEROFILL AUTO_INCREMENT NOT NULL, IMPORT_FIRST_DATE DATE NOT NULL, IMPORT_LAST_DATE DATE NOT NULL, PRIMARY KEY(IMPORT_ROW_ID), UNIQUE(IMPORT_HASH), INDEX(IMPORT_FIRST_DATE), INDEX(IMPORT_LAST_DATE), INDEX(IMPORT_HASH,IMPORT_ROW_ID) ) /*PARTITION BY HASH(DAYOFMONTH(IMPORT_FIRST_DATE)) PARTITIONS 31*/; delete from idx_datafeed_Resi_Expanded_HIST where IMPORT_FILE_DATE >= '2008-08-21'; delete from idx_datafeed_Resi_Expanded_CURR where IMPORT_FILE_DATE >= '2008-08-21'; delete from idx_datafeed_Resi_Expanded_DATA where IMPORT_FIRST_DATE >= '2008-08-21'; create table IF NOT EXISTS idx_datafeed_Resi_Expanded_TMP ( `LN` TINYTEXT NOT NULL, `TYP` TINYTEXT NOT NULL, `COLISTER` TINYTEXT NOT NULL, `CLI` TINYTEXT NOT NULL, ` CRP` TINYTEXT NOT NULL, `GS` TINYTEXT NOT NULL, `HS` TINYTEXT NOT NULL, `JH` TINYTEXT NOT NULL, `PIN` TINYTEXT NO T NULL, `STR` TINYTEXT NOT NULL, `TOUR_URL` TINYTEXT NOT NULL, `TWN` TINYTEXT NOT NULL, `TXY` TINYTEXT NOT NULL, `STATE` TINYTEXT NOT NULL, `LAT` TINYTEXT NOT NULL, `LNG` TINYTEXT NOT NULL, `REMARKS` TINYTEXT NOT NULL, IMPORT_ HASH BINARY(20) ); delimiter | create trigger idx_datafeed_trg1 AFTER INSERT ON idx_datafeed_Plus_Residential_TMP FOR EACH ROW BEGIN IF NEW.`TYP` in ('DE','AT','MU','MH','DP','MF','CO') and NEW.`CIT` = 'CHICAGO' THEN insert into idx_datafeed_Plus_Residential_DATA VALUES (NEW.`LN`, NEW.`TYP`, NEW.`UD`, NEW.`LAG`, NEW.`LO`, NEW.`OFFICE_PHONE`, NEW.`AGENT_FORM_NAME`, NE W.`OFFICE_FORM_NAME`, NEW.`LP`, NEW.`HSN`, NEW.`CP`, NEW.`STR`, NEW.`UN`, NEW.`CIT`, NEW.`ADI`, NEW.`AR`, NEW.`AR EA_Name`, NEW.`SUB`, NEW.`CNY`, NEW.`RMS`, NEW.`BR`, NEW.`BTH`, NEW.`MBB`, NEW.`CARS`, NEW.`GAR`, NEW.`PKN`, NEW. `PAR`, NEW.`FP`, NEW.`BLT`, NEW.`LSZ`, NEW.`ACR`, NEW.`WF`, NEW.`BMT`, NEW.`MOD`, NEW.`TAX`, NEW.`TXY`, NEW.`ASM` , NEW.`MBS`, NEW.`MBF`, NEW.`B2S`, NEW.`B2F`, NEW.`B3S`, NEW.`B3F`, NEW.`B4S`, NEW.`B4F`, NEW.`LRS`, NEW.`LRF`, N EW.`DRS`, NEW.`DRF`, NEW.`KTS`, NEW.`KTF`, NEW.`FRS`, NEW.`FRF`, NEW.`RE1`, NEW.`RE2`, NEW.`RE3`, NEW.`RE4`, NEW. `RE5`, NEW.`RE6`, NEW.`RE7`, NEW.`RE8`, NEW.`RE9`, NEW.`RE10`, NEW.`A1N`, NEW.`A1S`, NEW.`A1F`, NEW.`A2N`, NEW.`A 2S`, NEW.`A2F`, NEW.`A3N`, NEW.`A3S`, NEW.`A3F`, NEW.`A4N`, NEW.`A4S`, NEW.`A4F`, NEW.`A5N`, NEW.`A5S`, NEW.`A5F` , NEW.`GS`, NEW.`GSD`, NEW.`JH`, NEW.`JHD`, NEW.`HS`, NEW.`HSD`, NEW.`OT`, NEW.`OSD`, NEW.`FEA`, NEW.`OTR`, NEW.` STY`, NEW.`TPE`, NEW.`TPC`, NEW.`TMU`, NEW.`TMF`, NEW.`DIR`, NEW.`DR1`, NEW.`DR2`, NEW.`DR3`, NEW.`UFL`, NEW.`FLN `, NEW.`ASF`, NEW.`PTA`, NEW.`CAA`, NEW.`EXP`, NEW.`UFE`, NEW.`TNU`, NEW.`UF1`, NEW.`RM1`, NEW.`BR1`, NEW.`BT1`, NEW.`UF2`, NEW.`RM2`, NEW.`BR2`, NEW.`BT2`, NEW.`UF3`, NEW.`RM3`, NEW.`BR3`, NEW.`BT3`, NEW.`UF4`, NEW.`RM4`, NEW .`BR4`, NEW.`BT4`, NEW.`GRI`, NEW.`NOI`, NEW.`AP1`, NEW.`AP2`, NEW.`AP3`, NEW.`AP4`, NEW.`AZN`, NEW.`SLN`, NEW.`A SQ`, NEW.`TLA`, NEW.`RU`, NEW.`LT`, NEW.`AML`, NEW.`BIM`, NEW.`CUU`, NEW.`FMT`, NEW.`LND`, NEW.`LOCAT`, NEW.`PTU` , NEW.`GSI`, NEW.`TAE`, NEW.`GSA`, NEW.`TO`, NEW.`UNT`, NEW.`SUBTPE`, NEW.`RP`, NEW.`MIN`, NEW.`MAX`, NEW.`BSQ`, NEW.`BAG`, NEW.`BUT`, NEW.`APT`, NEW.`OFC`, NEW.`STO`, NEW.`DID`, NEW.`NDK`, NEW.`MXF`, NEW.`MXI`, NEW.`MIF`, NEW .`MII`, NEW.`HT`, NEW.`GD`, NEW.`Park`, NEW.`DBL`, NEW.`MK`, NEW.`NBR_PHOTOS`, NEW.`SUPP_PHOTOS`, NEW.`ZP`, NEW.` Tour_URL`, NEW.`ST`, NEW.`AGE`,NEW.IMPORT_HASH,'','2008-08-21','2008-08-21') ON DUPLICATE KEY update IMPORT_LAST_DATE = '2008-08-21'; /* important: do not use Variables in triggers, it's very slow! ATR 8/16/2008 */ replace into idx_datafeed_Plus_Residential_HIST VALUES ( (select IMPORT_ROW_ID from idx_datafeed_Plus_Residential_DATA where IMPORT_HASH = NEW.IMPORT_HASH), '2008-08-21', NEW.`LN`); replace into idx_datafeed_Plus_Residential_CURR VALUES ( (select IMPORT_ROW_ID from idx_datafeed_Plus_Residential_DATA where IMPORT_HASH = NEW.IMPORT_HASH), '2008-08-21', NEW.`LN`); END IF; END; | create trigger idx_datafeed_trg2 AFTER INSERT ON idx_datafeed_Resi_Expanded_TMP FOR EACH ROW BEGIN IF (SELECT COUNT(*) FROM idx_datafeed_Plus_Residential_CURR where `LN`=NEW.`LN` and IMPORT_FILE_DATE='2008-08-21') = 1 THEN insert into idx_datafeed_Resi_Expanded_DATA VALUES (NEW.`LN`, NEW.`TYP`, NEW.`COLISTER`, NEW.`CLI`, NEW.`CRP`, NEW.`GS`, NEW.`HS`, NEW.`JH`, NEW.`PIN `, NEW.`STR`, NEW.`TOUR_URL`, NEW.`TWN`, NEW.`TXY`, NEW.`STATE`, NEW.`LAT`, NEW.`LNG`, NEW.`REMARKS`, NEW.IMPORT_ HASH,'','2008-08-21','2008-08-21') ON DUPLICATE KEY update IMPORT_LAST_DATE = '2008-08-21'; replace into idx_datafeed_Resi_Expanded_HIST VALUES ( (select IMPORT_ROW_ID from idx_datafeed_Resi_Expanded_DATA where IMPORT_HASH = NEW.IMPORT_HASH), '2008-08-21', NEW.`LN`); replace into idx_datafeed_Resi_Expanded_CURR VALUES ( (select IMPORT_ROW_ID from idx_datafeed_Resi_Expanded_DATA where IMPORT_HASH = NEW.IMPORT_HASH), '2008-08-21', NEW.`LN`); END IF; END; | delimiter ; insert into idx_datafeed_file_commit (IMPORT_FILE_DATE,START_TIMESTAMP,COMMIT_TIMESTAMP) values ('2008-08-21',NOW (),null); SELECT NOW() AS `IMPORT_START_TM_Plus_Residential` \G LOAD DATA LOCAL INFILE 'filestore/[login to view URL]' REPLACE INTO TABLE idx_datafeed_Plus_Residential_ TMP FIELDS TERMINATED BY '|' IGNORE 1 LINES SET IMPORT_HASH=UNHEX(SHA(CONCAT_WS('|',`LN`,`TYP`,`UD`,`LAG`,`LO`,`OFFICE_PHONE`,`AGENT_FORM_NAME`,`OFFICE_FORM_ NAME`,`LP`,`HSN`,`CP`,`STR`,`UN`,`CIT`,`ADI`,`AR`,`AREA_Name`,`SUB`,`CNY`,`RMS`,`BR`,`BTH`,`MBB`,`CARS`,`GAR`,`PK N`,`PAR`,`FP`,`BLT`,`LSZ`,`ACR`,`WF`,`BMT`,`MOD`,`TAX`,`TXY`,`ASM`,`MBS`,`MBF`,`B2S`,`B2F`,`B3S`,`B3F`,`B4S`,`B4F `,`LRS`,`LRF`,`DRS`,`DRF`,`KTS`,`KTF`,`FRS`,`FRF`,`RE1`,`RE2`,`RE3`,`RE4`,`RE5`,`RE6`,`RE7`,`RE8`,`RE9`,`RE10`,`A 1N`,`A1S`,`A1F`,`A2N`,`A2S`,`A2F`,`A3N`,`A3S`,`A3F`,`A4N`,`A4S`,`A4F`,`A5N`,`A5S`,`A5F`,`GS`,`GSD`,`JH`,`JHD`,`HS `,`HSD`,`OT`,`OSD`,`FEA`,`OTR`,`STY`,`TPE`,`TPC`,`TMU`,`TMF`,`DIR`,`DR1`,`DR2`,`DR3`,`UFL`,`FLN`,`ASF`,`PTA`,`CAA `,`EXP`,`UFE`,`TNU`,`UF1`,`RM1`,`BR1`,`BT1`,`UF2`,`RM2`,`BR2`,`BT2`,`UF3`,`RM3`,`BR3`,`BT3`,`UF4`,`RM4`,`BR4`,`BT 4`,`GRI`,`NOI`,`AP1`,`AP2`,`AP3`,`AP4`,`AZN`,`SLN`,`ASQ`,`TLA`,`RU`,`LT`,`AML`,`BIM`,`CUU`,`FMT`,`LND`,`LOCAT`,`P TU`,`GSI`,`TAE`,`GSA`,`TO`,`UNT`,`SUBTPE`,`RP`,`MIN`,`MAX`,`BSQ`,`BAG`,`BUT`,`APT`,`OFC`,`STO`,`DID`,`NDK`,`MXF`, `MXI`,`MIF`,`MII`,`HT`,`GD`,`Park`,`DBL`,`MK`,`NBR_PHOTOS`,`SUPP_PHOTOS`,`ZP`,`Tour_URL`,`ST`,`AGE`))); SELECT NOW() AS `IMPORT_FINISHED_Plus_Residential` \G analyze table idx_datafeed_Plus_Residential_HIST; analyze table idx_datafeed_Plus_Residential_CURR; analyze table idx_datafeed_Plus_Residential_DATA; SELECT NOW() AS `IMPORT_START_TM_Resi_Expanded` \G LOAD DATA LOCAL INFILE 'filestore/[login to view URL]' REPLACE INTO TABLE idx_datafeed_Resi_Expanded_TMP FIELDS TERMINATED BY '|' IGNORE 1 LINES SET IMPORT_HASH=UNHEX(SHA(CONCAT_WS('|',`LN`,`TYP`,`COLISTER`,`CLI`,`CRP`,`GS`,`HS`,`JH`,`PIN`,`STR`,`TOUR_URL`,` TWN`,`TXY`,`STATE`,`LAT`,`LNG`,`REMARKS`))); SELECT NOW() AS `IMPORT_FINISHED_Resi_Expanded` \G analyze table idx_datafeed_Resi_Expanded_HIST; analyze table idx_datafeed_Resi_Expanded_CURR; analyze table idx_datafeed_Resi_Expanded_DATA; * * *This broadcast message was sent to all bidders on Saturday Aug 30, 2008 5:02:56 PM: Sorry, the formatting got all messed up in the above, try seeing it here: [login to view URL] * * *This broadcast message was sent to all bidders on Sunday Aug 31, 2008 9:52:47 AM: **Here is some important additional info, please read.** The same process is executed daily (with a new input file obviously). Each day, approximately 90% of the rows in the file are identical to the previous day (value of IMPORT_HASH is same). I'm willing to any of the following provided that there is a significant boost in performance: I'm willing to upgrade MySQL in order to use the BLACKHOLE engine for the trigger table, and also to use Partitioning (which is commented out due to required MySQL server version needs to be upgraded first), since most of the slowdown appears to be related to the inserts done within the trigger. I'm willing to eliminate the use of IMPORT_HASH. I'm willing to accept other ideas if the cost/benefit is good. I'm not willing to dramatically alter the logical design of the database - the 3-table arrangement with DATA (data entity table), HIST (historical reference table), and CURR (current reference table) has to stay in place. I am willing to accept small tweaks of the schema. Sample input files (2-day span) are available for qualified bidders who ask for it. * * *This broadcast message was sent to all bidders on Sunday Aug 31, 2008 9:57:50 AM: Please note the recommended bidding approach for this project. You should try some changes in your local environment first before you bid. That way you can tell me the expected percentage improvement in performance in the Bid itself, which then if I agree with it becomes the requirement. ## Platform MySQL Server version: 5.0.22-Debian_0ubuntu6.06.10-log