Optimizing MySQL UPDATE-Statements->Howto?
Posted: Wed Aug 03, 2005 3:26 am
Hello,
Following UPDATE-statement varies very strong in its execution time.:
UPDATE firmendaten SET id_cca = '11' , id_callcenter = '1' , comment_cca = '*** EX-Adresse von XY ***neu0303 -----' ,
comment_vkl_hidden = '*** EX-Adresse von XY ***', status = '' WHERE idfirmendaten = '53752'
From 0,3 sec to 2,7 sec. I'm using MySQL 4.1.12 together with PHP-Version: 5.0.4. My table 'firmendaten' holds 220.000 records, and consists of about 60 fields.
What is strange is that on my old server with MySQL 4.0.24 and PHP-Version: 4.2.2 and also about 220.000 records, such _simliar_ UPDATE-statements are executed much faster and first of all constantly without fluctuations.
I must admit, my new table 'firmendaten' has grown a bit, but not substantially. I don't think that's the problem.
=> Since the field 'idfirmendaten' is a PRIMARY KEY, to my understanding, MySQL uses this index for the UPDATE-execution, since 'idfirmendaten' is used in the WHERE part. Is there anything I can do to accelerate such UPDATE. To explain further, I'm not talking about 1 or 2 UPDATES, that wouldn't be a problem, but of up to 500 UPDATES one behind the other. You can imagine how long such updates would take in a total....
Comment: I have deleted most of the fields from the table 'firmendaten'
--
-- Tabellenstruktur für Tabelle `firmendaten`
--
CREATE TABLE `firmendaten` (
`idfirmendaten` int(10) unsigned NOT NULL auto_increment,
`comment_cca` text character set latin1 collate latin1_german1_ci NOT NULL,
`comment_vkl_hidden` text NOT NULL,
`id_cca` int(10) unsigned NOT NULL
`id_callcenter` tinyint(3) unsigned NOT NULL defaul
PRIMARY KEY (`idfirmendaten`),
KEY `firmennameIndex` (`firmenname`(50)),
KEY `bearbeitenCallcenterIndex` (`id_callcenter`,`idfirmendaten`),
KEY `bearbeitenCcaIndex` (`id_cca`,`status`),
KEY `bearbeitenAdminIndex` (`status`,`termin_cca_date`,`termin_client_date`),
KEY `produkteIndex` (`produkte`(200)),
FULLTEXT KEY `volltextIndexFirmenname` (`firmenname`),
FULLTEXT KEY `volltextIndex` (`werbetext`,`marken`,`strasse`,`ort`,`produkte`),
FULLTEXT KEY `volltextIndexProdukte` (`produkte`),
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=227618 ;
Following UPDATE-statement varies very strong in its execution time.:
UPDATE firmendaten SET id_cca = '11' , id_callcenter = '1' , comment_cca = '*** EX-Adresse von XY ***neu0303 -----' ,
comment_vkl_hidden = '*** EX-Adresse von XY ***', status = '' WHERE idfirmendaten = '53752'
From 0,3 sec to 2,7 sec. I'm using MySQL 4.1.12 together with PHP-Version: 5.0.4. My table 'firmendaten' holds 220.000 records, and consists of about 60 fields.
What is strange is that on my old server with MySQL 4.0.24 and PHP-Version: 4.2.2 and also about 220.000 records, such _simliar_ UPDATE-statements are executed much faster and first of all constantly without fluctuations.
I must admit, my new table 'firmendaten' has grown a bit, but not substantially. I don't think that's the problem.
=> Since the field 'idfirmendaten' is a PRIMARY KEY, to my understanding, MySQL uses this index for the UPDATE-execution, since 'idfirmendaten' is used in the WHERE part. Is there anything I can do to accelerate such UPDATE. To explain further, I'm not talking about 1 or 2 UPDATES, that wouldn't be a problem, but of up to 500 UPDATES one behind the other. You can imagine how long such updates would take in a total....
Comment: I have deleted most of the fields from the table 'firmendaten'
--
-- Tabellenstruktur für Tabelle `firmendaten`
--
CREATE TABLE `firmendaten` (
`idfirmendaten` int(10) unsigned NOT NULL auto_increment,
`comment_cca` text character set latin1 collate latin1_german1_ci NOT NULL,
`comment_vkl_hidden` text NOT NULL,
`id_cca` int(10) unsigned NOT NULL
`id_callcenter` tinyint(3) unsigned NOT NULL defaul
PRIMARY KEY (`idfirmendaten`),
KEY `firmennameIndex` (`firmenname`(50)),
KEY `bearbeitenCallcenterIndex` (`id_callcenter`,`idfirmendaten`),
KEY `bearbeitenCcaIndex` (`id_cca`,`status`),
KEY `bearbeitenAdminIndex` (`status`,`termin_cca_date`,`termin_client_date`),
KEY `produkteIndex` (`produkte`(200)),
FULLTEXT KEY `volltextIndexFirmenname` (`firmenname`),
FULLTEXT KEY `volltextIndex` (`werbetext`,`marken`,`strasse`,`ort`,`produkte`),
FULLTEXT KEY `volltextIndexProdukte` (`produkte`),
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=227618 ;