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`)
) ENGINE=MyISAM AUTO_INCREMENT=687605 DEFAULT CHARSET=latin1;
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.