Question :
In my MYSQL configuration I activated logging of request not using indexes.
slow_query_log=1
slow_query_log_file = /home/mysql/sqlprive.log
long_query_time=7
log_queries_not_using_indexes =1
But in my log file I find some requests that are not slow and are using indexes:
# Query_time: 0.001160 Lock_time: 0.000188 Rows_sent: 10 Rows_examined: 20
SELECT `t`.`id` AS `t0_c0`, `t`.`nom` AS `t0_c2`, some others fields whitout indexes
FROM `my_table` `t` GROUP BY t.id ORDER BY t.name ASC
LIMIT 10 OFFSET 10;
Here is the structure of my table
CREATE TABLE IF NOT EXISTS `my_table` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
#some other fields whitout any indexes
PRIMARY KEY (`id`),
UNIQUE KEY `name` (`name`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1060 ;
Someone has an idea why I find this kind of request in the slow log file?
Answer :
The option log_queries_not_using_indexes
is probably the cause.
From the MySQL Server Option
If you are using this option with the slow query log enabled, queries
that are expected to retrieve all rows are logged. See Section 5.2.5,
“The Slow Query Logâ€. This option does not necessarily mean that no
index is used. For example, a query that uses a full index scan uses
an index but would be logged because the index would not limit the
number of rows.
So, your query is a “not using index query” and a “full scan query”…
mysql> explain select * from yourTable LIMIT 10 OFFSET 100G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: yourTable
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 124386
Extra:
1 row in set (0.01 sec)