Optimizing MySQL UPDATE-Statements->Howto?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
visionmaster
Forum Contributor
Posts: 139
Joined: Wed Jul 14, 2004 4:06 am

Optimizing MySQL UPDATE-Statements->Howto?

Post by visionmaster »

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 ;
Post Reply