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!
