Re: two column int index not used correctly

[Ming Lu]

Hi, Jørgen

Thanks:)
And sorry for the late response.

EXPLAIN 
SELECT
    A.*,
    ip_range_table.ip_class
FROM web_query.web_query_ip_dest_analysis_20110616171614564_merged AS A
    JOIN ip_range_table FORCE INDEX (idx_start_ip) ON A.ip>=ip_range_table.start_ip;

The output is:

+----+-------------+----------------+------+---------------+------+---------+------+---------+--------------------------------+
| id | select_type | table          | type | possible_keys | key  | key_len | ref  | rows    | Extra                          |
+----+-------------+----------------+------+---------------+------+---------+------+---------+--------------------------------+
|  1 | SIMPLE      | ip_range_table | ALL  | idx_start_ip  | NULL | NULL    | NULL |    1347 |                                |
|  1 | SIMPLE      | A              | ALL  | NULL          | NULL | NULL    | NULL | 1566774 | Using where; Using join buffer |
+----+-------------+----------------+------+---------------+------+---------+------+---------+--------------------------------+

And I also tried this:

SELECT
    A.*,
    ip_range_table.ip_class
FROM web_query.web_query_ip_dest_analysis_20110616171614564_merged AS A
    JOIN ip_range_table FORCE INDEX FOR JOIN (idx_start_ip) ON A.ip>=ip_range_table.start_ip;

The output is same:

+----+-------------+----------------+------+---------------+------+---------+------+---------+--------------------------------+
| id | select_type | table          | type | possible_keys | key  | key_len | ref  | rows    | Extra                          |
+----+-------------+----------------+------+---------------+------+---------+------+---------+--------------------------------+
|  1 | SIMPLE      | ip_range_table | ALL  | idx_start_ip  | NULL | NULL    | NULL |    1347 |                                |
|  1 | SIMPLE      | A              | ALL  | NULL          | NULL | NULL    | NULL | 1566774 | Using where; Using join buffer |
+----+-------------+----------------+------+---------------+------+---------+------+---------+--------------------------------+