How to enable Slow Query Log in MySQL Wamp Server

When I was working in code migration project, I got chance to fine tune code as well as database side. Everything was fine while running the site, but when its come to load testing its creating issue with select queries side.

Due to the un-optimized queries, which was creating slow queries and site become too slow.
But how we recognize its was from slow queries, but it may be in code side also right.

While code migration each and every module was working fine, when it was combined as entry site it was creating a problem. So instead of checking the each and every select queries in code part.

We plan to lock the Slow queries, I will tech you how to enable slow queries in wamp server.

What is Slow Query ?
Query which took more than long_query_time seconds to execute, default values for long_query_time are 0 and 10.

How to log the Slow Query in Wamp Server
In Wamp server click the mysql.ini file, search for the slow_query.
You can able to see four variables for slow query logs.

Wamp Server my.ini file

Wamp Server my.ini file

1) slow_query_log
– which enable / disable the slow queries logs.

2) slow_query_log_file
– log the logs of the slow queries with given path.

3) long_query_time
– query will log which take more than mentioned seconds in long_query_time variable.

4) log_queries_not_using_indexes
– queries which not using index will be logged in slow query.

Now let’s enable slow query in wamp server mysql.ini file.

slow_query_log=1
slow_query_log_file=d:/mysql-bin/slowquery.log

long_query_time=10
log_queries_not_using_indexes=0

Above one log the slow queries which take more the 10 seconds, also we have disable log_queries_not_using_indexes. If you want to log not using index queries, you can enable by setting 1.

lets execute sql statement which will take less than 10 seconds.
select sleep(9);

mysql slow query empty result

mysql slow query empty result

Now execute sql statement, which will take more than 10 seconds.
select sleep(10);

tail mysql slow queries

tail mysql slow queries

Two output have different values, first one show empty result set, Because select query run less than mentioned long queries. Second one display data like time, host, query time, lock time, rows set & rows examined.

Also its display which query took long time with timestamp, So you can easily identified the slow query and optimizer it.

If you have question related to slow queries, please let me know.

Share