Isn't the choice in the the "type" col of Explain a "full index scan" rather than a full table scan ?My understanding was that the "ALL" stands for "full table scan ".
Thanks, Mariella On Tue, Jul 29, 2008 at 11:03 AM, Mary Bahrami <[EMAIL PROTECTED]>wrote: > 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 >
