MySQL Performance: MySQL => From time to time very slow UPDATE

From time to time very slow UPDATE [message #978]

Fri, 23 March 2007 13:23

4matic
Messages: 2
Registered: March 2007

Junior Member

I have a system:
Windows XP SP2
MySQL 5.0.27
PHP 5.2.0 (mysqli ext)

CREATE TABLE `parts` (                                
          `partid` int(10) unsigned NOT NULL auto_increment,  
          `oenumsearch` char(30) NOT NULL,                    
          `tmid` smallint(5) unsigned NOT NULL default '0',   
          `oenum` char(35) NOT NULL,                          
          `isset` smallint(5) unsigned default '0',           
          `numtypeid` smallint(5) unsigned default '0',       
          `textid` int(10) unsigned default NULL,             
          `texts` char(255) default NULL,                     
          `isttx` tinyint(1) unsigned NOT NULL default '0',   
          `isimg` tinyint(1) unsigned NOT NULL default '0',   
          `isauto` tinyint(1) unsigned NOT NULL default '0',  
          `remarks` char(255) default NULL,                   
          PRIMARY KEY  (`partid`),                            
          KEY `tmid` (`tmid`),                                
          KEY `oenums` (`oenumsearch`,`tmid`),                
          KEY `oenumsearch` (`oenumsearch`),                  
          KEY `isset` (`isset`),                              
          KEY `numtypeid` (`numtypeid`),                      
          KEY `textid` (`textid`)                             
        ) ENGINE=InnoDB

I start transaction and update some tables. One of the updated tables is `parts`. When start query:

UPDATE `parts` SET `textid`=1471, `texts`="Some text" WHERE `partid`=47437

Database is stuck, and query executed about 300-400 seconds. In this time I cant start another query. Another query is wait while ends query for update.
This stuks is happend form time to time. Only one client sends query, thats means database is not busy.

Where looking for problem? I dont uderstand what to do. Help me, please.

P.S. Sorry for my dreadful English, my native language is PHP Smile

[Updated on: Fri, 23 March 2007 13:31]

Report message to a moderator