Bug #7939 --log-queries-not-using-indexes doesnt apply with 0 or 1 row OR quuery cache
Submitted: 16 Jan 2005 16:33 Modified: 17 Feb 2005 17:36
Reporter: Martin Friebe (Gold Quality Contributor) (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.1.9 OS:
Assigned to: Jim Winstead CPU Architecture:Any

[16 Jan 2005 16:33] Martin Friebe
Description:
the example contains some queries, that do *not* use an index *and* are *not* logged to slow.log.  

there are also queries, which are logged, but explain states, they do use an index.

How to repeat:
#configure your server with  --log-queries-not-using-indexes
create table t1 (a integer); alter table t1 add index (a);

# table is empty, no index is used, but no entry in slow.log

select * from t1;
explain select * from t1;
+----+-------------+-------+--------+---------------+------+---------+------+------+---------------------+
| id | select_type | table | type   | possible_keys | key  | key_len | ref  | rows | Extra               |
+----+-------------+-------+--------+---------------+------+---------+------+------+---------------------+
|  1 | SIMPLE      | t1    | system | NULL          | NULL |    NULL | NULL |    0 | const row not found |
+----+-------------+-------+--------+---------------+------+---------+------+------+---------------------+

# also not in log:
select * from t1 where a=1; # impossible where instead of index

# table with 1 row, no index used, but will not be in slow log 
insert into t1 values (1);

select * from t1;
explain select * from t1;
+----+-------------+-------+--------+---------------+------+---------+------+------+-------+
| id | select_type | table | type   | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+-------+--------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | t1    | system | NULL          | NULL |    NULL | NULL |    1 |       |
+----+-------------+-------+--------+---------------+------+---------+------+------+-------+

select * from t1 where a=1;
explain select * from t1 where a=1;
+----+-------------+-------+--------+---------------+------+---------+------+------+-------+
| id | select_type | table | type   | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+-------+--------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | t1    | system | a             | NULL |    NULL | NULL |    1 |       |
+----+-------------+-------+--------+---------------+------+---------+------+------+-------+

# table with 2 rows 
insert into t1 values (1);

# *** The next query appears in the slow log. but explain states, that it is using the index. ***
select * from t1;
explain select * from t1;
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | t1    | index | NULL          | a    |       5 | NULL |    2 | Using index |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+

#the query with where, is handled as expected with 2 or more rows in the table.
 select * from t1 where a = 1 ;

# with regards to the query cache
# use table without index
create table t2 (a integer); insert into t2 values (1), (2), (3);

select a from t2 ; # appears in slow.log (correct)
select a from t2 ; # does not appear in slow.log
reset query cache ;
select a from t2 ; # appears in slow.log (correct)

Suggested fix:
-This should either be changed, or documented.
[21 Jan 2005 5:35] Dean Ellis
Verified, thank you for the report.

Some comments: the INSERT would not be logged in any case; the query which is logged but for which the EXPLAIN shows an index being used is performing a full index scan.
[3 Feb 2005 0:41] Jim Winstead
This is an issue for documentation. Queries handled by the query cache won't get added to the slow query log, nor will queries that would not benefit from the presence of an index because the table has no entries or only one.
[17 Feb 2005 17:36] Paul Dubois
I'll add Jim's observations to:

http://dev.mysql.com/doc/mysql/en/slow-query-log.html