Re: two column int index not used correctly

[Jørgen Løland]

Hi Ming,

Thinking of it, there is actually a possibility for MySQL to consider range access also in non-constant cases. Take a look here:

http://dev.mysql.com/doc/refman/5.5/en/explain-output.html (See "Range checked for each record")

Can you modify case 3) like this and report back with the results?

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;

("LEFT" was removed)

Hope this helps,
Jørgen Løland
Software Engineer, MySQL, Oracle
jorgenloland.blogspot.com