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
>
