Re: two column int index not used correctly
Posted by:
Ming Lu
Date: June 15, 2011 06:18AM
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:)
Subject
Views
Written By
Posted
3863
May 27, 2011 10:06AM
1693
June 06, 2011 03:15AM
Re: two column int index not used correctly
1427
June 15, 2011 06:18AM
1088
June 15, 2011 07:14AM
1084
June 15, 2011 08:07PM
1254
June 15, 2011 11:30PM
999
June 16, 2011 06:23AM
1626
June 17, 2011 01:20AM
1083
June 17, 2011 01:34AM
1027
June 20, 2011 12:20AM
1082
June 20, 2011 11:49PM
1099
June 22, 2011 12:05AM
1128
June 16, 2011 08:58AM
1350
June 17, 2011 01:00AM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.