Hi, Jorgen
Many thanks again:)
And I tried as following.
The case 1 shows I can use a non-const variable to filter on the left-join table.
The case 2 shows that range can be applied.
The case 3 shows that rang can NOT be applied in left join, is it true? I don't understand this, why? I think it has no difference with case 2.
case 1.
EXPLAIN
SELECT
A.*,
ip_range_table.ip_class
FROM web_query.web_query_ip_dest_analysis_20110616171614564_merged AS A
LEFT 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 | A | ALL | NULL | NULL | NULL | NULL | 4440673 | |
| 1 | SIMPLE | ip_range_table | ref | idx_start_ip | idx_start_ip | 8 | web_query.A.ip | 1 | |
+----+-------------+----------------+------+---------------+--------------+---------+----------------+---------+-------+
case 2.
EXPLAIN
SELECT
*
FROM ip_range_table FORCE INDEX (idx_start_ip)
WHERE start_ip >= 10000000000;
The output is:
+----+-------------+----------------+-------+---------------+--------------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------------+-------+---------------+--------------+---------+------+------+-------------+
| 1 | SIMPLE | ip_range_table | range | idx_start_ip | idx_start_ip | 8 | NULL | 2 | Using where |
+----+-------------+----------------+-------+---------------+--------------+---------+------+------+-------------+
case 3.
EXPLAIN
SELECT
A.*,
ip_range_table.ip_class
FROM web_query.web_query_ip_dest_analysis_20110616171614564_merged AS A
LEFT 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 | A | ALL | NULL | NULL | NULL | NULL | 4440673 | |
| 1 | SIMPLE | ip_range_table | ALL | idx_start_ip | NULL | NULL | NULL | 1347 | |
+----+-------------+----------------+------+---------------+------+---------+------+---------+-------+
(Per my understanding, mysql would go through the table A row by row, and for each row, mysql can get a A.ip, it would use this to filter the left join table ip_range_table by the join condition. Is this understanding correct?)