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.
