I’m puzzled why this query often comes up in the slow queries:
UPDATE aliro_remosef_uri SET sef = ‘/forum/func,view/id,11093/catid,15/page,2/’, sef_crc = CRC32(‘/forum/func,view/id,11093/catid,15/page,2/’), refreshed = 1269087250, marker = 1 - marker, ipaddress = ‘65.55.109.166’ WHERE uri_crc = AND uri = ‘option=com_aliroboard&func=view&id=11093&catid=15&page=2’
The equivalent SELECT normally operates very fast. Although the SQL looks complex, the application of CRC32 to the main search string yields an integer that is tested against an indexed field. Often the CRC value is unique - it is in this example.
So I guess it is the fact that it is an UPDATE that makes the operation slow. The table is structured as:
CREATE TABLE IF NOT EXISTS aliro_remosef_uri
( id
int(11) NOT NULL auto_increment, marker
tinyint(4) NOT NULL default ‘0’, shortterm
tinyint(4) NOT NULL default ‘0’, refreshed
int(11) NOT NULL default ‘0’, sef_crc
int(11) unsigned NOT NULL default ‘0’, uri_crc
int(11) unsigned NOT NULL default ‘0’, ipaddress
varchar(15) NOT NULL, sef
text NOT NULL, uri
text NOT NULL, PRIMARY KEY (id
), UNIQUE KEY refreshed
(refreshed
,id
), KEY sef_crc
(sef_crc
), KEY uri_crc
(uri_crc
)) ENGINE=MyISAM DEFAULT CHARSET=utf8;
Maybe TEXT fields are hard to handle? Can anything be done to make this table work faster in UPDATEs?