Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
GROUP BY "primary key" makes no sense, here, unless you have sanitized this query to the point that it doesn't convey the nature of what's actually being executed... and if so, this is probably changing the query plan in a negative way that is not using indexes properly or optimally. If you believe the query to be logically-valid as written, please post the output of EXPLAIN SELECT. –  Michael - sqlbot Jan 29 at 13:06
    
You r right, the group by as no sense! The query is auto generated by a module that can add some join to the query so the group by may have sense in others queries the module is executing. Do you thing this is the reason why the request appears in the slow log? –  darkheir Jan 29 at 13:12

1 Answer 1

up vote 0 down vote accepted

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) 
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.