MYSQL using a spatial index
I am trying to use a spatial index. I have an ips table and an ip2geo table with ip block ranges. I am trying to assign Geo ID to each ip from ip2geo table
When trying to select using a column value, the Spatial Index is not used.
EXPLAIN
SELECT *,
( SELECT locid FROM `ipblocks` i
WHERE MBRCONTAINS(i.ippolygon,
POINTFROMWKB(POINT(h.`ip`, 0))) ) AS locaid
FROM `ips` h LIMIT 1;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY h ALL NULL NULL NULL NULL 33279 2 DEPENDENT
SUBQUERY i ALL ipblock_spatialidx NULL NULL NULL 4977388 Using where
When using a constant, the filter uses an index.
EXPLAIN SELECT *,(SELECT locid FROM `ipblocks` i WHERE
MBRCONTAINS(i.ippolygon, POINTFROMWKB(POINT(3223394542, 0))) ) AS
locaid FROM `ips` h LIMIT 1;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY h ALL NULL NULL NULL NULL 33279 Using filesort 2 UNCACHEABLE
SUBQUERY i range ipblock_spatialidx ipblock_spatialidx 34 NULL 1 Using where
When using inner index join (check additionally)
EXPLAIN SELECT * FROM `ips` h INNER JOIN `ipblocks` i ON (MBRCONTAINS(i.ippolygon, POINTFROMWKB(POINT(h.`cp`, 0)))) LIMIT 100 ;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE h ALL NULL NULL NULL NULL 33279
1 SIMPLE i ALL ipblock_spatialidx NULL NULL NULL 4977388
The range is marked for each entry (index map: 0x1)
When there is no left join, the index is not used.
EXPLAIN SELECT * FROM `ips` h LEFT JOIN `ipblocks` i ON (MBRCONTAINS(i.ippolygon, POINTFROMWKB(POINT(h.`ip`, 0)))) LIMIT 100 ;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE h ALL NULL NULL NULL NULL 33279
1 SIMPLE i ALL ipblock_spatialidx NULL NULL NULL 4977388
How do I optimize my SQL queries to use a spatial index?
UPDATE:
I was able to quickly assign the GEO country using the insert trigger. But I still need to know why I can't use Spatial index when joining or subquery
BEGIN
DECLARE geoloc VARCHAR(10) DEFAULT NULL;
SELECT country FROM ipblocks i LEFT JOIN iplocations l ON(i.locid=l.locid) WHERE MBRCONTAINS(i.ippolygon, POINTFROMWKB(POINT(NEW.ip, 0))) LIMIT 1 INTO geoloc;
SET NEW.geo= geoloc;
END
UPDATE 2 Question for @John
My goal is to take a table IPs
with the following schema
username, ipaddress, country
And use the GEO2IP table I purchased that comes with IP ranges like INET_ANOT () table IPblocks
ipfrom,ipto,country,poly [example POLYGON((16777216 -1,16777471 -1,16777471 1,16777216 1,16777216 -1)) ]
Now without creating a trigger or stored procedure, how can I update the country in the table IPs
using the geospatial index fromIPblocks
LATEST UPDATE (PROVIDING) USED SOLUTION
SELECT * FROM `iplist` i LEFT JOIN `iplocations` l ON (SELECT GetLocId(INET_ATON(i.`ip`))=l.`locid`) ;
GetLocId uses the following SQL
SELECT locid FROM `ipblocks` i WHERE
MBRCONTAINS(i.ippolygon, POINTFROMWKB(POINT(@INPUTVAR, 0))) INTO locid
and returns locid, it matches 40k ips in 39ms
source to share
What you are seeing is, unfortunately, a common problem with the way spatial functions are implemented in MySQL and the associated disadvantages with subqueries involving spatial functions.
For the Contains and Intersections functions to work correctly, and the index used requires one of the geometries to be a constant. This does not seem to be documented, although all the examples you will see with MySQL with Intersects / Contains work this way.
So, you cannot write something like this like you could in Oracle Spatial or Postgis,
select a.*, b.*
from sometable a, someothertable b
where ST_Intersects(a.geom, b.geom)
and a.someattribute=... and b.someattribute=...;
In such a query, if tables a and b have spatial indexes, they will be used if this is more restrictive than any other attribute you can put in the where clause.
The same goes for self-joins, where you want to find all polygons that intersect with all other polygons in the table based on some attribute, for example
select a.*
from sometable a, sometable b
where ST_Intersects(a.geom, b.geom) ....
So, in MySQL space, you are forced to have one of the constant geometries.
As with the left, the left join syntax doesn't make much sense spatially (although supported), since you are not really joining a single match attribute, but a two-dimensional contain / intersect operator.
Also, I'm pretty sure your inner join is not using the index if you look at key
and the rows
output of the explanation.
source to share