Large MySQL Table Update Slow

Question

Hi this is my table structure

CREATE TABLE IF NOT EXISTS `sms_report` (
 `R_id` int(11) NOT NULL auto_increment,
 `R_uid` int(11) NOT NULL,
 `R_smppid` varchar(100) collate utf8_unicode_ci NOT NULL,
 `R_from` varchar(10) collate utf8_unicode_ci NOT NULL,
 `R_status` longtext collate utf8_unicode_ci NOT NULL,
 `R_message` text collate utf8_unicode_ci NOT NULL,
 `R_numbers` longtext collate utf8_unicode_ci NOT NULL,
 `R_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
 `R_timedate` varchar(40) collate utf8_unicode_ci NOT NULL,
 `R_show` int(11) NOT NULL default '1',
 `oldformat` tinyint(1) NOT NULL default '0',
 PRIMARY KEY  (`R_id`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1947722 ;

I have approx 2 million rows so when I do an update on R_status here, it seem to take too long(R_status is either 1,2,16 or 24). Please suggest on how to optimise.

Answer 1

If R_status is always integer, make it integer. Also, I would try to convert this table into fixed-row-width format (no varchars/texts)

Also, make index on R_smppid, without that it would do full table scan on each update.

Answer 2

As @BarsMonster suggestd, convert R_status into an integer (TINYINT if values are only 1,2,16,24), and create an INDEX on R_smppid. Also, if R_smppid is fixed width, change field type to char(40) or whatever the length of the content is, or if it can be converted into an integer, that's even better.