List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:April 17 1999 8:39am
Subject:BUG REPORT: Index usage anomalies
View as plain text  
Hi!

>>>>> "Randolph" == Randolph Chung <randolph@stripped> writes:

Randolph> Symptoms: MySQL uses the index for some range queries and not others, even
Randolph> on the same field. It does not seem to do this consistently.

MySQL will only use index on a column if it thinks that it will be faster
to retrieve the rows by index instead of by table scanning.

<cut>

Randolph> Problems:

mysql> explain select * from bar where start_date = 19900101;
Randolph>
> +-------+------+---------------+------------+---------+------+------+-------+
Randolph> | table | type | possible_keys | key        | key_len | ref  | rows | Extra
> |
Randolph>
> +-------+------+---------------+------------+---------+------+------+-------+
Randolph> | bar   | ref  | start_date    | start_date |       8 | ???  |    1 |      
> |
Randolph>
> +-------+------+---------------+------------+---------+------+------+-------+
Randolph> 1 row in set (0.00 sec)

mysql> explain select * from bar where start_date > 19900101;
Randolph>
> +-------+-------+---------------+------------+---------+------+------+-------+
Randolph> | table | type  | possible_keys | key        | key_len | ref  | rows | Extra
> |
Randolph>
> +-------+-------+---------------+------------+---------+------+------+-------+
Randolph> | bar   | range | start_date    | start_date |    NULL | NULL | 1045 |      
> |
Randolph>
> +-------+-------+---------------+------------+---------+------+------+-------+
Randolph> 1 row in set (0.01 sec)

mysql> explain select * from bar where start_date < 19900101;
Randolph>
> +-------+------+---------------+------+---------+------+-------+------------+
Randolph> | table | type | possible_keys | key  | key_len | ref  | rows  | Extra     
> |
Randolph>
> +-------+------+---------------+------+---------+------+-------+------------+
Randolph> | bar   | ALL  | start_date    | NULL |    NULL | NULL | 10865 | where used
> |
Randolph>
> +-------+------+---------------+------+---------+------+-------+------------+
Randolph> 1 row in set (0.02 sec)

As you can see, in the last entry the number of rows is much
larger. In this case it makes sense to use ALL instead of range.

Randolph> Notice that the first two queries uses the index, but the last one doesn't.
Randolph> (this is on the solaris machine)

<cut>

Randolph> Any pointers on how to get the index to work on all queries is much
Randolph> appreciated. Please send replies to me personally. I will summarize
> responses.

In the later case it's better that MySQL doesn't use indexes as the
query will be resolved faster in this case!  The problem with using
indexes is that the rows are retrieved in a random order instead of
sequentially. 

The rule to determinate when to use table scanning is the following:

#define TIME_FOR_COMPARE 10        // 10 compares == one read 

read_time=(ha_rows) (((data_file_length)/IO_SIZE)+1+
	              records / TIME_FOR_COMPARE);

If the number of rows matched by the index is higher than the above,
then table scanning is used.

Regards,
Monty
Thread
BUG REPORT: Index usage anomaliesRandolph Chung17 Apr
  • BUG REPORT: Index usage anomaliesMichael Widenius17 Apr
    • Re: BUG REPORT: Index usage anomaliesRandolph Chung17 Apr
      • Re: BUG REPORT: Index usage anomaliesMichael Widenius18 Apr