Log QUERIES not using INDEX - MySQL

Question

I'm using MySQL Server version: 5.5.8-log MySQL Community Server (GPL)
I want to log queries which are not using INDEX and is slow too !
I'm copying here my my.ini settings.

[mysqld]
port=3306
log = "E:/wamp/logs/genquery.log"

log_slow_queries
long_query_time = 1
slow_query_log = 1
slow_query_log_file = "E:/wamp/logs/slowquery.log"

what change i need to do ?

For other copy-pasters: `log_slow_queries` isn't an option that exists in MySQL >=5.1; it was replaced by `slow_query_log` - see https://stackoverflow.com/questions/20136926/why-does-log-slow-queries-break-my-cnf — kuilin, Nov 13 '19 at 16:30

score 41 · Accepted Answer · answered Jun 21 '11 at 16:14

41

log_queries_not_using_indexes =1 //(or Yes) (From mysql)

answered Jun 21 '11 at 16:14

a1ex07

36,826
12
90
103

8

Is this a good idea to use these days though? because you end up with GB's of slow query logs because queries taking fractions of seconds will be logged in cases where indexes aren't used because no index is faster – wired00 May 20 '13 at 05:58
This will however log queries which are not using indexes regardless of if they exceed long_query_time. The question asked for queries which didn't have indexes AND are long running. – ClearCrescendo Aug 19 '15 at 17:25
this will log queries not using indexes regardeless of the long_query_time val? – Arnold Roa Apr 19 '16 at 21:44
1

After setting this parameter, where do I look for a file that MySQL is writing these queries to? – Allen King Apr 17 '17 at 18:28
In MySQL 5.7, according to the log it doesn't recognize the value "Yes" for booleans and so it sets it to "OFF". But "On" works. – orrd May 12 '17 at 20:27

score 9 · Answer 2 · answered Apr 23 '17 at 00:10

Maybe useful for Linux user. (Testet: Ubuntu 16.04)

Get root in terminal and edit mysql configuration

su
vim /etc/mysql/conf.d/mysql.cnf

[mysqld]
slow_query_log=1
slow_query_log_file=/var/log/mysql/slow-query.log
long_query_time=1
log_queries_not_using_indexes=1

Add log file and restart mysql server

touch /var/log/mysql/slow-query.log
chown mysql:adm /var/log/mysql/slow-query.log
chmod 640 slow-query.log
service mysql restart

Test slow logging with SQL queries

/* Activate query log - Maybe useful to show errors (not necessary) */
SET GLOBAL SLOW_QUERY_LOG=ON;

/* Check if slow query log is working */
SELECT SLEEP(2);

These days, if possible, it's best to put these sorts of localized mods into a file in the system folder /etc/my.cnf.d - eg: /etc/my.cnf.d/slow-logs - then in order to remove the lines, you can just remove the file. — Brian C, May 19 '21 at 03:54

score 6 · Answer 3 · edited Apr 18 '14 at 15:56

log_queries_not_using_indexes

Command-Line Format --log-queries-not-using-indexes
Option-File Format  log-queries-not-using-indexes
Option Sets Variable    Yes, log_queries_not_using_indexes
Variable Name   log_queries_not_using_indexes
Variable Scope  Global
Dynamic Variable    Yes
    Permitted Values
Type    boolean

Whether queries that do not use indexes are logged to the slow query log. See Section 5.2.4,

score 1 · Answer 4 · edited Mar 09 '23 at 09:03

In addition to a1ex07's answer you can use the shell command mk-query-digest to output a report of your running queries without using the log.

See the full methodology: https://web.archive.org/web/20131123010403/http://www.xaprb.com/blog/2009/08/18/how-to-find-un-indexed-queries-in-mysql-without-using-the-log/

As mentioned in the article it is also possible to group the queries by tables doing --group-by tables --report-format profile

Useful to fast detect unindexed queries.

Log QUERIES not using INDEX - MySQL

4 Answers4

Linked