List:General Discussion« Previous MessageNext Message »
From:Brett Error Date:April 12 1999 11:29pm
Subject:Not using the index
View as plain text  
mysql> show keys from banner100000;
+--------------+------------+----------+--------------+-------------+-----------+-------------+----------+
| Table        | Non_unique | Key_name | Seq_in_index | Column_name |
Collation | Cardinality | Sub_part |
+--------------+------------+----------+--------------+-------------+-----------+-------------+----------+
| banner100000 |          1 | start    |            1 | start       | A
|        NULL |     NULL |
| banner100000 |          1 | start    |            2 | end         | A
|        NULL |     NULL |
+--------------+------------+----------+--------------+-------------+-----------+-------------+----------+
2 rows in set (0.00 sec)


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

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


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

mysql> explain select * from banner100000 where start <= 10000000 and end
>= 10000000;
+--------------+-------+---------------+-------+---------+------+------+-------+
| table        | type  | possible_keys | key   | key_len | ref  | rows |
Extra |
+--------------+-------+---------------+-------+---------+------+------+-------+
| banner100000 | range | start         | start |    NULL | NULL | 2033 |
|
+--------------+-------+---------------+-------+---------+------+------+-------+
1 row in set (0.00 sec)


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

Thanks in advance,

Brett 


Thread
Not using the indexBrett Error13 Apr
  • Not using the indexMichael Widenius13 Apr