Insert ... on key update Performance

[php_dave]

Hello All,

 

Sorry if this has been posted before - I couldnt find anything.

 

I have a table:

 

# Table "service_order_command" DDL
CREATE TABLE `service_order_command` (
  `SOC_ID` int(11) NOT NULL,
  `SOC_COMMAND_ID` int(11) default NULL,
  `SOC_COM_REF_ID` int(11) default NULL,
  `SOC_CLI` int(11) default NULL,
  `SOC_ACCOUNT_NO` varchar(11) default NULL,
  `SOC_STATUS` varchar(255) default NULL,
  `SOC_LAST_UPDATE_TIME` date default NULL,
  `SOC_INSERT_TIME` date default NULL,
  `SOC_GO_LIVE_DATE` date default NULL,
  `SOC_ERROR_CODE` int(11) default NULL,
  `SOC_RESELLER_REFERENCE` varchar(255) default NULL,
  PRIMARY KEY  (`SOC_ID`),
  UNIQUE KEY `SOC_ID` (`SOC_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='InnoDB free: 2046976 kB; InnoDB free: 2049024 kB; InnoDB fre'

 

This table holds around 150k rows and grows by around 2 - 3k a day - Unfortuantley due to some very bad db design (not me!) I have to run an insert   update on key every 2hrs.  The issue is the performance is extremley poor - its taking around 3hrs !!  and the server that mysql is running on goes to 100%CPU at the time too.

 

Have I made a school boy error or is it expected to be this slow?

 

Thanks

Dave

[fenway]

What's your sql statment?