How to set slow query log


I want to set slow query log which consists of all SQL statements that took more than long_query_time seconds to execute.

Now default value is set for long_query_time ie 10. Show variables shows

log_slow_queries - OFF

long_query_time - 10

I want to enable log_slow_queries and set long_query_time =1

I found in mysql manual to set slow query log as follows in my.cnf


You must create the file manually and change owners this way:

mkdir /var/log/mysql
touch /var/log/mysql/log-slow-queries.log
chown mysql.mysql -R /var/log/mysql

Even after changing this in my.cnf, the show Global variables shows as

log_slow_queries - OFF

long_query_time - 10

It looks like same before and the new value not set.

In my linux server, my.cnf settings are


Default to using old password format for compatibility with mysql 3.x

clients (those using the mysqlclient10 compatibility package).




“/etc/my.cnf” 21L, 454C

Any one say how to find slow log queries. I’m using RHEL 5 and MySQL 5.0.22


have u restarted the mysql server after making your changes in the my.cnf file

S . I restarted using command - service mysqld restart

General query log captures in mysqld.log.

slow query log only not enabled.

After restarting mysql server log_slow_queries=off

is there any other way to enable log_slow_queries


This is one of the most called query:

select * from delivery_zones where
ST_Contains(zone, POINT(latitude,longitude))
and restaurant_id = 100228 and
(weekdays & 16 or weekdays is null)
and (
(hour_starts_at is null and hour_ends_at is null)
or (hour_starts_at is null and hour_ends_at >= ‘13:15:00’)
or (hour_starts_at <= ‘13:15:00’ and hour_ends_at is null)
or (‘13:15:00’ BETWEEN hour_starts_at AND hour_ends_at)
and (
(starts_at is null and ends_at is null)
or (starts_at is null and ends_at >= ‘2018-09-21 13:15:00’)
or (starts_at <= ‘2018-09-21 13:15:00’ and ends_at is null)
or (‘2018-09-21 13:15:00’ BETWEEN starts_at AND ends_at)
and active = 1
and delivery_zones.deleted_at is null

order by layer asc;

(i’ve replaced the real latitude and longitude when posting here, for privacy)
I would like to avoid the resulting filesort

this is the explain:

1,SIMPLE,delivery_zones,range,zone,active,weekdays,restaurant_id,deleted_at,hours,starts_ends,zone,34,1,69.93,Using where; Using filesort

There is also another query called tons of time in a row (about 700-800 times per access) but it doesn’t result in slow log, and I don’t know why, probably it’s already optimized

Hi gandalf did you intend to update this post? It’s from 2007! :slight_smile: