I think this is really not normal, but I'm seeing very simple MySQL UPDATE queries take >500x longer than a similar SELECT query.
According to MySQL, "An update statement is optimized like a SELECT query with the additional overhead of a write."
I read that as an UPDATE is as fast as a similar SELECT, just adding the time to write to disk added on.
So why, even when my writes are NULL, does the query take sooo much longer?
an example of some timer values for similar SELECT/UPDATE queries
SELECT * FROM db_feeds.products_merch1 WHERE Product_ID LIKE '25732' took 0.000439 seconds.
UPDATE db_feeds.products_merch1 SET Sale_Price=NULL, Shipping_Price=NULL WHERE Product_ID = 25732 took 0.153917 seconds.
SELECT * FROM db_feeds.products_merch1 WHERE Product_ID LIKE '75031' took 0.000356 seconds.
UPDATE db_feeds.products_merch1 SET Sale_Price=NULL, Shipping_Price=NULL WHERE Product_ID = 75031 took 0.155337 seconds.
SELECT * FROM db_feeds.products_merch1 WHERE Product_ID LIKE '66829' took 0.000376 seconds.
UPDATE db_feeds.products_merch1 SET Sale_Price=NULL, Shipping_Price=NULL WHERE Product_ID = 66829 took 0.155480 seconds.
SELECT * FROM db_feeds.products_merch1 WHERE Product_ID LIKE '76324' took 0.000349 seconds.
UPDATE db_feeds.products_merch1 SET Sale_Price=NULL, Shipping_Price=NULL WHERE Product_ID = 76324 took 0.154164 seconds.
The table db_feeds.products_merch1 has only one key, the primary key, which is Product_ID. There are 19,178 rows and which take up 16 MB (the PRIMARY key takes 145 KB of that)
Please, please any help on trying to speed up my update queries would be v.much appreciated.
BTW, table type is MyISAM - switching it to InnoDB make the UPDATE queries take an average of 0.42 seconds (SELECT the same time as MyISAM).
Andy
