I am trying to join two tables on the criteria that the value one column of
table1 is between the values of two columns of
CREATE TABLE `values`( `id` INT, `name` VARCHAR(50), `num_addr` BIGINT UNSIGNED );
CREATE TABLE `ranges`( `id` INT, `range_name` VARCHAR(50), `range_start` BIGINT UNSIGNED, `range_end` BIGINT UNSIGNED, INDEX `idx_start` (`range_start` ASC), INDEX `idx_end` (`range_end` ASC), INDEX `idx_range` (`range_start` ASC, `range_end` ASC) );
SELECT `v`.`name`, `v`.`num_addr`, `r`.`range_name` FROM `values` `v` LEFT JOIN `ranges` `r` ON `v`.`num_addr` BETWEEN `range_start` AND `range_end`
EXPLAIN EXTENDED for the query shows that no index is used, with the information 'Range checked for each record (index map: 0x7)'. This is a performance issue as the ranges table has over 500,000 rows and the query is time sensitive.
# id, select_type, table, type, possible_keys, key, key_len, ref, rows, filtered, Extra 1, SIMPLE, r, ALL, idx_start,idx_end,idx_range, , , , 1, 100.00, Range checked for each record (index map: 0x7)
FORCE INDEX ON JOIN actually makes things worse as the optimizer only sees the suggested index, but does not use it.
Is there a way to use an index on such joins?
- Changing the
value >= range_start AND value <= range_enddoes not change the execution plan.
idx_endindexes does not improve the situation.
- Adding an index to
num_addrdoes not affect the join execution.
- The intervals
(range_start, range_end)can overlap.
- There are
num_addrthat are not in a range.
- A good analogy is phone numbers: In the
rangestable there would be a record:
('UK', 44000000000000, 44999999999999)and another
('UK Vodafone', 44700000000000, 44799999999999).