Not the answer you need?
Register and ask your own question!

Slow query with CRC32

counterpointcounterpoint EntrantCurrent User Role Beginner
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 = '' WHERE uri_crc = AND uri = 'option=com_aliroboard&func=view&id=11093&catid=15&page=2'</pre>

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;</pre>

Maybe TEXT fields are hard to handle? Can anything be done to make this table work faster in UPDATEs?


  • xaprbxaprb Mentor Inactive User Role Beginner
    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.
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.