Do you have a slow MySQL update/insert/delete query?
Obviously, for ‘SELECT’ queries you can prepend the query with “EXPLAIN ” – however that doesn’t work for the other query types (UPDATE/INSERT/DELETE).
So, one solution which may explain why the query is slow is to turn on MySQL’s profiling functionality, like in the following example :
mysql> set profiling=1; Query OK, 0 rows affected (0.00 sec) mysql> update table_b set field_1 = 'something' where id = 5; Query OK, 1 row affected (9.27 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> show profile; +------------------------------+----------+ | Status | Duration | +------------------------------+----------+ | starting | 0.000054 | | checking permissions | 0.000005 | | Opening tables | 0.000017 | | System lock | 0.000005 | | Waiting for table level lock | 9.235670 | | System lock | 0.000030 | | init | 0.000053 | | Updating | 0.000066 | | end | 0.000004 | | Waiting for query cache lock | 0.000135 | | end | 0.032402 | | query end | 0.000005 | | closing tables | 0.000022 | | freeing items | 0.000012 | | logging slow query | 0.000002 | | cleaning up | 0.000002 | +------------------------------+----------+ 16 rows in set (0.00 sec)
In the above case, you can see the query was waiting for a table level lock for nearly 10 seconds.
As it turned out, the server was moderately busy and often running SELECT/read queries which included our table_b as a join, and these queries were often taking 20-30 seconds to complete.
As ‘table_b’ is of type MyISAM, MySQL waits until it can get a write lock on the table – and it can’t do this until the SELECT query has finished.
So, possible solutions :
- Remove the join on ‘table_b’ in the time consuming query – perhaps by rewriting the long running query
- Change ‘table_b’ to InnoDB using “ALTER TABLE table_b Engine=InnoDB”