CODEWITHSUNDEEP
mysql
Rajan
Rajan

Reputation: 75

Slow query log is logging faster queries in log file

I've setup MySQL slow query log on my database server and set the long query time to 5.
Just checked the log and its logging queries that take only milliseconds. Anyone know why that would be? Here's some of the log.

I am using mysql sloq query log and set the following line in my.cnf file:
slow_query_log=1
long_query_time=5
log-output=FILE
log-queries-not-using-indexes

but my log file show like :

# User@Host: root[root] @ localhost []
# Query_time: 0.077108  Lock_time: 0.000042 Rows_sent: 0  Rows_examined: 292
SET timestamp=1389868398;
UPDATE `cp_sessions` SET `last_activity` = 1389868398 WHERE session_id = 'e9f397b37812e7e5b0563de6a32d181b';
# User@Host: root[root] @ localhost []
# Query_time: 0.022188  Lock_time: 0.000072 Rows_sent: 0  Rows_examined: 292
SET timestamp=1389868398;
UPDATE `cp_sessions` SET `last_activity` = 1389868398, `user_id` = NULL, `user_data` = 'a:3:{s:9:\"game_name\";s:5:\"poker\";s:3:\"utm\";N;s:5:\"url_1\";s:23:\"http://adda52merge.org/\";}' WHERE `session_id` = 'e9f397b37812e7e5b0563de6a32d181b';
# User@Host: root[root] @ localhost []
# Query_time: 0.000465  Lock_time: 0.000068 Rows_sent: 1  Rows_examined: 292
SET timestamp=1389868398;
SELECT *
FROM (`cp_sessions`)
WHERE `session_id` = '8c34926f9f6d711bdc14eeeb40b7db7b'
AND `ip_address` = '192.168.1.235'
AND `user_agent` = 'Mozilla/5.0 (Windows NT 6.1; WOW64; rv:27.0) Gecko';
# User@Host: root[root] @ localhost []
# Query_time: 0.033085  Lock_time: 0.000036 Rows_sent: 0  Rows_examined: 292
SET timestamp=1389868398;
UPDATE `cp_sessions` SET `last_activity` = 1389868398 WHERE session_id = '8c34926f9f6d711bdc14eeeb40b7db7b';
# User@Host: root[root] @ localhost []
# Query_time: 0.000350  Lock_time: 0.000055 Rows_sent: 1  Rows_examined: 292
SET timestamp=1389868398;
SELECT *
FROM (`cp_sessions`)
WHERE `session_id` = '8c34926f9f6d711bdc14eeeb40b7db7b';
# User@Host: root[root] @ localhost []
# Query_time: 0.031274  Lock_time: 0.000043 Rows_sent: 0  Rows_examined: 292
SET timestamp=1389868398;
UPDATE `cp_sessions` SET `login_attempt` = 0 WHERE `session_id` = '8c34926f9f6d711bdc14eeeb40b7db7b';
# User@Host: root[root] @ localhost []
# Query_time: 0.000298  Lock_time: 0.000029 Rows_sent: 1  Rows_examined: 292
SET timestamp=1389868398;
SELECT *
FROM (`cp_sessions`)
WHERE `user_id` = '113';
# User@Host: root[root] @ localhost []
# Query_time: 0.000479  Lock_time: 0.000080 Rows_sent: 1  Rows_examined: 292
SET timestamp=1389868398;
SELECT *
FROM (`cp_sessions`)
WHERE `session_id` = '4809d7eef185f2bf3d8e38e1858a637c'
AND `ip_address` = '192.168.1.235'
AND `user_agent` = 'Mozilla/5.0 (Windows NT 6.1; WOW64; rv:27.0) Gecko';
# User@Host: root[root] @ localhost []
# Query_time: 0.022236  Lock_time: 0.000037 Rows_sent: 0  Rows_examined: 292
SET timestamp=1389868398;
UPDATE `cp_sessions` SET `user_data` = '', `user_id` = 0 WHERE `user_id` = '113';

Please help me where I am wrong and what can i do for this slow query log I want to execute that query which take more time for execution.

Upvotes: 2

Views: 673

Answers (1)

Mad Dog Tannen
Mad Dog Tannen

Reputation: 7244

The setting log-queries-not-using-indexes will add any query that is not using indexes to the slow log.

This should be activated as it can easily help you optimize your queries.

If you want queries over a certain amount of seconds you can execute

SELECT * FROM mysql.slow_log WHERE query_time > 5

This will give queries with execution time over 5 seconds.

Upvotes: 2

Related Questions