# How to set slow query log

**URL:** <https://forums.percona.com/t/how-to-set-slow-query-log/355>\
**Category:** Other MySQL® Questions\
**Created:** [June 13, 2007, 1:01am UTC](https://forums.percona.com/t/how-to-set-slow-query-log/355 "2007-06-13T01:01:20Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![julian](https://avatars.discourse-cdn.com/v4/letter/j/74df32/32.png) [@julian](https://forums.percona.com/u/julian)\
**Post date:** [June 13, 2007, 1:01am UTC](https://forums.percona.com/t/how-to-set-slow-query-log/355/1 "2007-06-13T01:01:20Z")

</div>

Hi

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

mysqld]  
set-variable=long\_query\_time=1  
log-slow-queries=/var/log/mysql/log-slow-queries.log

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

[mysqld]  
datadir=/var/lib/mysql  
socket=/var/lib/mysql/mysql.sock  
set-variable=long\_query\_time=1  
log-slow-queries=/var/log/mysql/log-slow-queries.log

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

# clients (those using the mysqlclient10 compatibility package).

old\_passwords=1

[mysql.server]  
user=mysql  
basedir=/var/lib

[mysqld\_safe]  
log-error=/var/log/mysqld.log  
pid-file=/var/run/mysqld/mysqld.pid

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

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

thanks

---

<div class="post-metadata">

**Author:** ![babuno5](https://avatars.discourse-cdn.com/v4/letter/b/47e85d/32.png) [@babuno5](https://forums.percona.com/u/babuno5)\
**Post date:** [June 13, 2007, 7:19am UTC](https://forums.percona.com/t/how-to-set-slow-query-log/355/2 "2007-06-13T07:19:06Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![julian](https://avatars.discourse-cdn.com/v4/letter/j/74df32/32.png) [@julian](https://forums.percona.com/u/julian)\
**Post date:** [June 13, 2007, 11:35pm UTC](https://forums.percona.com/t/how-to-set-slow-query-log/355/3 "2007-06-13T23:35:27Z")

</div>

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

thanks

---

<div class="post-metadata">

**Author:** ![gandalf](https://avatars.discourse-cdn.com/v4/letter/g/59ef9b/32.png) [@gandalf](https://forums.percona.com/u/gandalf)\
**Post date:** [September 21, 2018, 3:41am UTC](https://forums.percona.com/t/how-to-set-slow-query-log/355/4 "2018-09-21T03:41:26Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![lorraine.pocklington](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/lorraine.pocklington/32/37_2.png) [@lorraine.pocklington](https://forums.percona.com/u/lorraine.pocklington)\
**Post date:** [September 21, 2018, 4:08am UTC](https://forums.percona.com/t/how-to-set-slow-query-log/355/5 "2018-09-21T04:08:03Z")

</div>

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