The optimizer made the correct choice when it did a full table scan where there were few rows...I wouldn't want to override the optimizer in this case.
Check that you updated statistics between these two queries (information_schema.statistics.cardinality). -----Original Message----- From: MySQLForum MySQLForum [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 29, 2008 6:35 AM To: mysql@lists.mysql.com Subject: log-queries-not-using-indexes and "Using index" in the Extra col of Explain 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 (which is not one of the field1,field2, and field4 fields) 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 mf -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
