2

I'm trying to LEFT JOIN a table to another table where a column value is between two column values in the second table, and it is not using an index when doing this seemingly simple query. It has something to do with using less than and greater than on the JOIN because it uses the index for an equals statement, and it uses the index for hardcoded ranges.

Here is a link to a SQL Fiddle : http://sqlfiddle.com/#!9/7e36a5/1

Here are the two tables I'm trying to join :

CREATE TABLE `ip_addresses` (
  `ip_long_start` int(11) unsigned DEFAULT NULL,
  `ip_long_end` int(11) unsigned DEFAULT NULL,
  `ip_start` varchar(15) NOT NULL,
  `ip_end` varchar(15) DEFAULT NULL,
  `as_number` int(11) DEFAULT NULL,
  `as_organization` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`ip_start`),
  KEY `IDX_SE` (`ip_long_start`,`ip_long_end`),
  KEY `IDX_IP` (`ip_start`,`ip_end`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `apache_log` (
  `host` varchar(32) DEFAULT NULL,
  `port` varchar(8) DEFAULT NULL,
  `ip` varchar(15) DEFAULT NULL,
  `ip_long` int(11) unsigned DEFAULT NULL,
  `date` datetime DEFAULT NULL,
  `method` varchar(16) DEFAULT NULL,
  `url` varchar(511) DEFAULT NULL,
  `status_code` smallint(6) DEFAULT NULL,
  `response_size` int(11) DEFAULT NULL,
  `referrer` varchar(2047) DEFAULT NULL,
  `user_agent` varchar(511) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

When I run the following query, it does not use any indexes, and it takes longer to run than I care to wait.

SELECT *
FROM importinfo.apache_log l
LEFT JOIN logs_net.ip_addresses i ON i.ip_long_start <= l.ip_long AND i.ip_long_end >= l.ip_long
LIMIT 100

I've even tried using FORCE INDEX (IDX_SE), but it still won't use the index.

Here is the output of the EXPLAIN :

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE l ALL 101242
1 SIMPLE i ALL IDX_SE 337702 Range checked for each record (index map: 0x2)

What am I missing? Is there a better way to do this?

0

1 Answer 1

3

That's a classic problem.

The Optimizer does not understand that only one range will match (because ranges do not overlap).

The solution is to us ORDER BY and LiMIT 1 against one of the bounds (start or end).

However, that requires having a way to handle gaps between valid ranges.

A discussion, based on IP addresses: http://mysql.rjweb.org/doc.php/ipranges (Both IPv4 and IPv6 are discussed.)

If ranges do overlap, then it is a messier problem.

Be aware that some services (such as AOL) will assign the same IP address to different users at different times.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.