Slow MySQL UPDATE query

Question

I have a mysql table containing 400,000 rows

whenever I run a PHP script to update one row, it takes about 3-4 seconds to do it.

How can I optimize the update query?

UPDATE `weakvocab` SET `times` = times+1, `wrong` = wrong+1, `mtime` = 1284369979 WHERE `owner` = 'owner_name' AND `vocID` = 'ID_number' AND `type` = 'type_name';

This query is about updating user data after answering a question, so I need a fast query to give user a better experience in loading the next question.

Thank you,

Answer 1

Are the columns in your WHERE condition indexed? Change the UPDATE to a SELECT to see how Mysql executes it:

EXPLAIN SELECT * FROM `weakvocab` WHERE `owner` = 'owner_name' AND `vocID` = 'ID_number' AND `type` = 'type_name';

and paste the result in here

Answer 2

You could try adding an index on (owner_name, vocID, type) so that the record to update can be found faster.