Re: two column int index not used correctly

[Ming Lu]

Thanks, Jorgen:)

I have a same question with Alexander's.

Per the guide, I use following sql:

select * from table_A force index (primary) where ip_start_int <= 3566268565 AND ip_end_int >= 3566268565;

I have created an primary key for (ip_start_int,ip_end_int) on table_A.

the explain is:
table | type | possible_keys | key | key_len | ref | rows | Extra
---------+-------+---------------+---------+---------+------+------+-------------
table_A | range | PRIMARY | PRIMARY | 8 | NULL | 891 | Using where

When I use the table_A in left join, however, mysql use nothing again.
select ...
from table_main
left join table_A force index for join (primary) on (table_main.ip>= table_A.ip_start_int and table_main.ip<=table_A.ip_end_int)
...;

The explain is:

table | type | possible_keys | key | key_len | ref | rows | Extra
-----------+------+---------------+-------+---------+-------+---------+----------------------------------------------
table_main | ALL | time | NULL | NULL | NULL | 1585715 | Using where; Using temporary; Using filesort
-----------+------+---------------+-------+---------+-------+---------+----------------------------------------------
talbe_A | ALL | PRIMARY | NULL | NULL | NULL | 1347 | Using where; Using join buffer

Could you please help me?

Thanks in advance:)