I have this query:
SELECT `country`
FROM `geoip_base`
WHERE 1840344811 BETWEEN `start` AND `stop`
It's badly use index (use, but parse big part of table) and work too slowly. I tried use ORDER BY and LIMIT, but it hasn't helped.
"start <= 1840344811 AND 1840344811 <= stop" works similar.
CREATE TABLE IF NOT EXISTS `geoip_base` (
`start` decimal(10,0) NOT NULL,
`stop` decimal(10,0) NOT NULL,
`inetnum` char(33) collate utf8_bin NOT NULL,
`country` char(2) collate utf8_bin NOT NULL,
`city_id` int(11) NOT NULL,
PRIMARY KEY (`start`,`stop`),
UNIQUE KEY `start` (`start`),
UNIQUE KEY `stop` (`stop`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
Table have 57,424 rows.
Explain for query "... BETWEEN START AND STOP ORDER BY START LIMIT 1":
using key stop
and get 24099 rows.
Without order and limit, mysql doesn't use keys and gets all rows.
If your table is MyISAM
, you can improve this query using SPATIAL
indexes:
ALTER TABLE
geoip_base
ADD ip_range LineString;
UPDATE geoip_base
SET ip_range =
LineString
(
Point(-1, `start`),
Point(1, `stop`)
);
ALTER TABLE
geoip_base
MODIFY ip_range NOT NULL;
CREATE SPATIAL INDEX
sx_geoip_range ON geoip_base (ip_range);
SELECT country
FROM geoip_base
WHERE MBRContains(ip_range, Point(0, 1840344811)
This article may be of interest to you:
Alternatively, if your ranges do not intersect (and from the nature of the database I except they don't), you can create a UNIQUE
index on geoip_base.start
and use this query:
SELECT *
FROM geoip_base
WHERE 1840344811 BETWEEN `start` AND `stop`
ORDER BY
`start` DESC
LIMIT 1;
Note the ORDER BY
and LIMIT
conditions, they are important.
This query is similar to this:
SELECT *
FROM geoip_base
WHERE `start` <= 1840344811
AND `stop` >= 1840344811
ORDER BY
`start` DESC
LIMIT 1;
Using ORDER BY / LIMIT
makes the query to choose descending index scan on start
which will stop on the first match (i. e. on the range with the start
closest to the IP
you enter). The additional filter on stop will just check whether the range contains this IP
.
Since your ranges do not intersect, either this range or no range at all will contain the IP
you're after.