slow selects
Hi Guys
I have a problem and I hope you can help me.....
I have the following table:
CREATE TABLE `tSMSOUT` (
`id` int(10) NOT NULL auto_increment,
`service_type` char(2) NOT NULL default '',
`source_addr` varchar(16) NOT NULL default '',
`destination_addr` varchar(15) NOT NULL default '',
`message_payload` text NOT NULL,
`user_message_reference` char(2) NOT NULL default '',
`time_submitted` varchar(14) NOT NULL default '',
`time_processed` varchar(14) NOT NULL default '',
`status` char(1) NOT NULL default '',
`errorinfo` varchar(5) NOT NULL default '',
`message_id` varchar(50) NOT NULL default '',
`sentby` varchar(50) NOT NULL default '',
`esm_class` tinyint(5) NOT NULL default '0',
`rule` varchar(100) default 'NONE',
`user` varchar(100) default 'NONE',
`submittedby` varchar(50) default 'NONE',
PRIMARY KEY (`id`),
KEY `index_status_sentby` (`sentby`,`status`),
KEY `indeX_msg_id` (`message_id`(10)),
KEY `index_main` (`destination_addr`,`status`,`message_id`),
KEY `index_status` (`status`)
) TYPE=InnoDB;
I have about 18 programs doing selects/inserts/updates on this table, the thing that seems to take the longest is the selects
select id,service_type,source_addr,destination_addr,message_payload,user_message_reference,esm_class from tSMSOUT where status='0' and sentby="196.11.240.100ISOL_001ConnectPRS" order by id asc limit 1
update tSMSOUT set status=1,message_id='2363',time_submitted='20060831153404',source_addr='278200304574521' where id='10284521'
update tSMSOUT set status=2,time_processed='20060831153432',message_id='24/00/3f7c50b8/1127820474499' where message_id='2487'
update tSMSOUT set status=3 ,time_processed='20060831153432' where status=2 and (message_id='24/00/3f7c4db8/1127820461744' and destination_addr='xxxxx') limit 1
Could anyone please help me getting the speed of the selects up.....
The reason for this is, with the current system, i can receive about 20000 sms's/minute and using the same band-width I need to be able to get the same out....(which i am not)
Any questions/suggestions would be greatly appreciated....
REgards
Danie