MySQL Forums
Forum List  »  Newbie

Update Speed problem
Posted by: Kal Bo
Date: March 31, 2009 04:29PM

I have a master_parts table with around 700k records, and is expected to grow up to 10 million, that is why I have decided to switch to MySQL.

Now, I'm having trouble using an update statement. I tried to replicate this in access 2007 and it only took like 10 seconds for about 1000 records in the update_category table to finish. There can be as many as half a million records to be updated from the update_category table.

Tried this on different mysql administration tools (phpmyadmin, heidisql, mysql front):

update master_parts as A inner join update_category as B
on A.sku=B.sku
set A.category=B.category

The table structure is this:
CREATE TABLE `master_parts` (
`master_parts_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`sku` varchar(53) NOT NULL DEFAULT '',
`manufacturer_code` char(3) NOT NULL,
`part_number` varchar(50) NOT NULL,
`short_description` varchar(255) DEFAULT NULL,
`long_description` text,
`category` varchar(3) DEFAULT NULL,
`weight` decimal(65,2) DEFAULT NULL,
`msrp` decimal(65,2) DEFAULT NULL,
`cost` decimal(65,2) DEFAULT NULL,
`has_image` int(1) DEFAULT NULL,
`smfrpc` varchar(1) DEFAULT NULL,
`frtcls` smallint(6) DEFAULT NULL,
`from` varchar(2) DEFAULT NULL,
PRIMARY KEY (`master_parts_id`),
FULLTEXT KEY `fulltextsearch` (`short_description`,`long_description`,`part_number`)

And it's driving me nuts why the update is so slow when in access it's fast enough. By the way, my machine is:

Intel Core i7 2.66GHz
12GB ram
OCZ SSD hard drive

Could it possibly be the hard drive? I heard that solid state drives are slow when writing.

Options: ReplyQuote

Written By
Update Speed problem
March 31, 2009 04:29PM
April 02, 2009 12:43AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.