Re: Keys ignored for larger tables!?

[tamas marki]

mysql> explain 
    -> select start.id 
    ->   from ( select id from arinrange where ip_start<=1582783255 ) start
    ->   join ( select id from arinrange where ip_end>=1582783255 ) end
    ->     on start.id = end.id;
+----+-------------+------------+-------+------------------+------------------+---------+------+---------+--------------------------+
| id | select_type | table      | type  | possible_keys    | key              | key_len | ref  | rows    | Extra                    |
+----+-------------+------------+-------+------------------+------------------+---------+------+---------+--------------------------+
|  1 | PRIMARY     | <derived3> | ALL   | NULL             | NULL             | NULL    | NULL |  674023 |                          | 
|  1 | PRIMARY     | <derived2> | ALL   | NULL             | NULL             | NULL    | NULL | 1316679 | Using where              | 
|  3 | DERIVED     | arinrange  | range | idx_end_start_id | idx_end_start_id | 4       | NULL |  727188 | Using where; Using index | 
|  2 | DERIVED     | arinrange  | range | idx_start_end_id | idx_start_end_id | 4       | NULL | 1263515 | Using where; Using index | 
+----+-------------+------------+-------+------------------+------------------+---------+------+---------+--------------------------+
4 rows in set (1.87 sec)

mysql> 
mysql> explain select id from arinrange where 1582783255 between ip_start and ip_end ;
+----+-------------+-----------+-------+-----------------------------------+------------------+---------+------+--------+--------------------------+
| id | select_type | table     | type  | possible_keys                     | key              | key_len | ref  | rows   | Extra                    |
+----+-------------+-----------+-------+-----------------------------------+------------------+---------+------+--------+--------------------------+
|  1 | SIMPLE      | arinrange | range | idx_start_end_id,idx_end_start_id | idx_end_start_id | 4       | NULL | 727188 | Using where; Using index | 
+----+-------------+-----------+-------+-----------------------------------+------------------+---------+------+--------+--------------------------+
1 row in set (0.00 sec)