List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:April 13 1999 2:01am
Subject:Not using the index
View as plain text  
Hi again!

>>>>> "Brett" == Brett Error <brette@stripped> writes:

mysql> show keys from banner100000;
Brett>
> +--------------+------------+----------+--------------+-------------+-----------+-------------+----------+
Brett> | Table        | Non_unique | Key_name | Seq_in_index | Column_name |
Brett> Collation | Cardinality | Sub_part |
Brett>
> +--------------+------------+----------+--------------+-------------+-----------+-------------+----------+
Brett> | banner100000 |          1 | start    |            1 | start       | A
Brett> |        NULL |     NULL |
Brett> | banner100000 |          1 | start    |            2 | end         | A
Brett> |        NULL |     NULL |
Brett>
> +--------------+------------+----------+--------------+-------------+-----------+-------------+----------+
Brett> 2 rows in set (0.00 sec)

mysql> explain select * from banner100000 where 10000 between start and
Brett> end;
Brett>
> +--------------+------+---------------+------+---------+------+--------+------------+
Brett> | table        | type | possible_keys | key  | key_len | ref  | rows   |
Brett> Extra      |
Brett>
> +--------------+------+---------------+------+---------+------+--------+------------+
Brett> | banner100000 | ALL  | NULL          | NULL |    NULL | NULL | 100000 |
Brett> where used |
Brett>
> +--------------+------+---------------+------+---------+------+--------+------------+
Brett> 1 row in set (0.00 sec)

For the moment MySQL only can use keys for BETWEEN statements of type:
key_column between constant_1 and constant_2

(On can't optimize everything and the above is not that useful to optimize).

Brett> The above query doesn't even see a possible key.  Why can't it use the key
Brett> across start and end.  Seems like it would be very helpful to do so...

Brett> If I rearrange the query to look like this and not use between:

mysql> explain select * from banner100000 where start <= 10000000 and end
>> = 10000000;

Brett>
> +--------------+-------+---------------+-------+---------+------+------+-------+
Brett> | table        | type  | possible_keys | key   | key_len | ref  | rows |
Brett> Extra |
Brett>
> +--------------+-------+---------------+-------+---------+------+------+-------+
Brett> | banner100000 | range | start         | start |    NULL | NULL | 2033 |
Brett> |
Brett>
> +--------------+-------+---------------+-------+---------+------+------+-------+
Brett> 1 row in set (0.00 sec)

Brett> Why doesn't it use the "end" part of the two-column key "start".  If it
Brett> did, it would see that there is only 1 possible row, not 2033.

EXPLAIN only shows the index name (in this case the index is named
'start').

Note that MySQL can't use a key when you select on 'end' like this!

The internal key table is first sorted on 'start' and thereafter on 'end'

For every value of start <= 10000000, you must examine how many values 
there are the has    end >= 10000000.

Condsider the sorted key table:

1, 0
1, 10000000
2, 0
2, 10000000
3, 0
3, 10000000
....
10000000, 0
10000000, 10000000
10000001, 0
10000001, 10000000


As it would take a VERY long time to find out how many 'possible'
matching rows there are in this case, EXPLAIN instead reports how many 
rows are before the key entry:

10000000,1000000

Hope this explains the problem in optimizing this.

Regards,
Monty

PS: Are you really sure that the above query will return what you
    really want?  It's looks very peculiar!
Thread
Not using the indexBrett Error13 Apr
  • Not using the indexMichael Widenius13 Apr