MySQL Forums
Forum List  »  Optimizer & Parser

log-queries-not-using-indexes and "Using index" in the Extra col of Explain
Posted by: MFTree MFTree
Date: July 28, 2008 01:10PM

Hi All,

I have been using mysql 5.1.26-rc on Linux and configured to log queries that do not use index through
log-queries-not-using-indexes

I would like to ask two questions, please:

1)
I have noticed that in some cases some queries get logged because a scan of the index on a field is executed.

Does the log-queries-not-using-indexes include also "index scans" ?

E.G.

EXPLAIN SELECT DISTINCT field1 FROM table1;

+----+-------------+-----------------+-------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------------+-------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | table1 | index | NULL | (field1,field2,field3) | 107 | NULL | 2 | Using index |
+----+-------------+-----------------+-------+---------------+------+---------+------+------+-------------+

In this case the table has only two rows so I suppose the index scan is executed because the set rows is small.


2)

In other cases with the same number of rows or a few more rows (e.g. 8 rows)
doing the same query triggers the "RANGE" type in the type column of explain, the key_len size decreases and "Using index for group-by" in the Extra col.

+----+-------------+----------------+-------+---------------+------+---------+------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------------+-------+---------------+------+---------+------+------+--------------------------+
| 1 | SIMPLE | table1 | range | NULL | (field1,field2,field3) | 98 | NULL | 2 | Using index for group-by |
+----+-------------+----------------+-------+---------------+------+---------+------+------+--------------------------+


In both cases the rows are all identical, they only differ on the value of the primary key
Is there a way to trigger this behavior all the times all the times (forcing the index does not help because the index is chosen in any case).

Thanks in advance for your help

Options: ReplyQuote


Subject
Views
Written By
Posted
log-queries-not-using-indexes and "Using index" in the Extra col of Explain
4759
July 28, 2008 01:10PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.