MySql turning – “Select full join” is too high – How to identify the queries causing this

If temporary tables are over utilized, my first thought is that the application using the database is in need of query optimization.

In the slow log, one of the fields displayed is the Lock Time

Here is a sample with two queries that were logged

# Time: 110726  1:00:09
# User@Host: mysql[mysql] @  [10.64.100.208]
# Query_time: 8  Lock_time: 0  Rows_sent: 0  Rows_examined: 852669
insert into rates_old select * from rates;
# Time: 110726  1:30:56
# User@Host: mysql[mysql] @  [10.64.100.208]
# Query_time: 50  Lock_time: 0  Rows_sent: 0  Rows_examined: 11015414
SET timestamp=1311661856;
delete a.*,  b.* from zillowDump a, zillowHistory b where a.loanRequestId=b.loanRequestId and a.addDate < date_sub(now(), INTERVAL 2 week);

Please note the Lock_time: field.

CAVEAT

In MySQL 5.1+ the field appears in the default mysql.slow_log table

mysql> show create table mysql.slow_log\G
*************************** 1. row ***************************
       Table: slow_log
Create Table: CREATE TABLE `slow_log` (
  `start_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `user_host` mediumtext NOT NULL,
  `query_time` time NOT NULL,
  `lock_time` time NOT NULL,
  `rows_sent` int(11) NOT NULL,
  `rows_examined` int(11) NOT NULL,
  `db` varchar(512) NOT NULL,
  `last_insert_id` int(11) NOT NULL,
  `insert_id` int(11) NOT NULL,
  `server_id` int(10) unsigned NOT NULL,
  `sql_text` mediumtext NOT NULL
) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='Slow log'
1 row in set (0.00 sec)

Either way, you can get a hold of the lock time of any query