Menu
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?
406
The fastest way to speed up the update query is to replace it with a bulk-insert operation. It is a minimally logged operation in simple and Bulk-logged recovery model. This can be done easily by doing a bulk-insert in a new table and then rename the table to original one.
Alter the query It can use a table that is suboptimal, or gather more information than necessary. Slow queries are frequently caused by combining two or more large tables together using a JOIN. Review the number of joins in your query, and determine if the query is pulling more information than is actually needed.
Queries can become slow for various reasons ranging from improper index usage to bugs in the storage engine itself. However, in most cases, queries become slow because developers or MySQL database administrators neglect to monitor them and keep an eye on their performance.
The way to make a query run faster is to reduce the number of calculations that the software (and therefore hardware) must perform. To do this, you'll need some understanding of how SQL actually makes calculations.
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.
178
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
