Mysql – Request shown in slow logs

indexMySQLperformanceslow-log

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?

Best 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 100\G
*************************** 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)
Related Question