[MySQL] Extremely slow UPDATE query

Question

Hi everyone.

I noticed that a script of mine became very slow, then I narrowed down to the problem: it was an Update query. The weird thing is that the SELECT query is very fast. The table has about 600,000 entries. And yes, id is UNIQUE PRIMARY KEY. Here are some examples:

SELECT * FROM `tmp_pages_data` WHERE id = 19080 LIMIT 0 , 30

Showing rows 0 - 0 (1 total, Query took 0.0004 sec)

And now the update query:

UPDATE tmp_pages_data SET page_status = 1 WHERE id = 19080

1 row(s) affected. ( Query took 24.5968 sec )

As you can see, the select is very fast, but the update is veery slow. How is this possible?

Answer 1

Yes, it's very weird. Only thing I can think of is that tmp_pages_data table is locked by other transaction, or row with id = 19080 is locked by other transaction.

The other (improbable thing) is that you have an index on page_status that needs to be updated on the UPDATE sentence, and that part is taking a lot of time to execute.

Answer 2

Ok, done!

I had to restart Apache, now it works great (actually I have rebooted Ubuntu)!

UPDATE tmp_pages_data SET page_status =1 WHERE id =19080

1 row(s) affected. ( Query took 0.0004 sec )

Thanks everyone for your suggestions :)