Slow Updates on Memory table - (detailed) - ANYONE??

[B L]

THE PROBLEM:
10k inserts takes 4 seconds
10k updates takes 6 minutes

THE PLATFORM:
MySQL 5.0.45
Sun 5220 Sparc 64, Solaris 10, 32GB RAM
Yes I have read all the other threads about slow updates.

I have this "memory" table (full table definition at end of post):
- 10k records
- hash key consisting of 3 fields
- no other indexes
- every key is unique

NOTES:
- 10k updates takes 4 seconds IF no records are found to update (indicating that the DB is not having a problem using the index to search for the record to update, but rather takes forever to update the record once found)
- BTREE index takes a little longer than HASH index
- myisam engine takes longer than memory engine
- the updated fields are NOT part of the key (and the key is the only index)
- 10k inserts and 10k updates in the order insert,update,insert,update,... is almost twice as fast as 10k inserts followed by 10k updates, but still takes 3.5 minutes where it should be under 30 seconds.
- in my test every inserted record is updated one time.
- tested using "isql" and "mysql" with batch file input


CREATE TABLE MYTDR (
TDR_CREATIONDATE_EPOCH int,
TDR_CREATIONDATE_NANO int,
TDR_SOURCEIP binary(4),
TDR_SOURCEPORT smallint,
TDR_COMPLETE tinyint,
TDR_TRANSACTIONID binary(12),
TDR_CODE smallint,
TDR_FOOID varchar(8) COLLATE latin1_general_cs,
TDR_ECCID varchar(9) COLLATE latin1_general_cs,
TDR_ACTUALESN int,
TDR_INTENDEDROUTED smallint,
TDR_ACTUALROUTED smallint,
TDR_ESRN varchar(16) COLLATE latin1_general_cs,
TDR_ESQK varchar(10) COLLATE latin1_general_cs,
TDR_ORIGINALCALLDATAID binary(12),
TDR_ORIGINALCALLDATE_EPOCH int,
TDR_ORIGINALCALLDATE_NANO int,
PRIMARY KEY (TDR_TRANSACTIONID, TDR_CREATIONDATE_EPOCH, TDR_CREATIONDATE_NANO))engine=memory;



Edited 1 time(s). Last edit at 03/11/2009 12:44PM by B L.