Announcement

Announcement Module
Collapse
No announcement yet.

Slow query with CRC32

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Slow query with CRC32

    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?

  • #2
    The equivalent SELECT won't run through the same codepath in the server, and of course it isn't changing any data. Does the UPDATE run slowly if you run it? The other question is what else is happening at the same time.

    Comment

    Working...
    X