MYSQL Using Spatial Index

Question

I am attempting to utilize the spatial index. I have a table of ips, and a ip2geo table with ip block ranges. I'm attempting to assign Geo ID to each ip from the ip2geo table

When attempting to to selected using a columns value the Spatial Index doesn't doesn't get 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 in the filter the index get used.

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 inner joining the index get used (check extra)

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

Range checked for each record (index map: 0x1)

When left joining there is no index 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 optimise my SQL queries to use a spatial index?

UPDATE:

I was able to quickly assign a GEO country by using a insert trigger. But I still need to know why I cannot use a Spatial index when joining or subquerying

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 a GEO2IP table i purchased which comes with IP ranges as INET_ANOT() table IPblocks

ipfrom,ipto,country,poly  [example POLYGON((16777216 -1,16777471 -1,16777471 1,16777216 1,16777216 -1)) ]

Now with out making a trigger, or a stored procedure how can I update country in table IPs using the geospatial index from ipblocks

LAST UPDATE (PROMISE) 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 matched 40k ips in 39ms

You probably don't like my answer very much, but I worked with MySQL spatial for a number of years (I was one of the beta testers for the new polygonal spatial functionality introduced in 5.6) and, sadly, the need to have one of the geometries be a constant is a fact. Subqueries involving spatial don't seem to work either. So, as long as you are OK with having a constant as in your trigger, you can work round it. But you aren't doing anything wrong, it is simply a limitation. — John Powell
– John Powell, Commented Aug 22, 2014 at 13:48

Amnon · Accepted Answer · 2015-01-15 14:03:13Z

8

What you is seeing, is, unfortunately, a general problem with the way spatial functions are implemented in MySQL and a related weakness with subqueries involving spatial functions.

For the Contains and Intersects functions to work properly, and for the index to be used, you need to have one of the geometries be a constant. This doesn't appear to be documented, although all the examples you will see with MySQL with Intersects/Contains work this way.

So, you can't write something like this, as 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 both have spatial indexes, they will be used, providing this is more restrictive than some other attribute you might put in the where clause.

The same applies for self joins, where you want to find all polygons that intersect with all other polygons in a table based on some attribute, eg,

select a.* 
from sometable a, sometable b 
where ST_Intersects(a.geom, b.geom) ....

So, in MySQL spatial you are forced to have one of the geometries be a constant.

As an aside, the left join syntax doesn't make much sense with spatial (although it is supported), as you are not really joining on a single matched attribute, but on a 2-dimensional containment/intersection operator.

Also, I'm pretty sure that on your inner join the index is not being used, if you look at the key and rows output of explain.

edited Jan 15, 2015 at 14:03

Amnon

2,3401 gold badge20 silver badges37 bronze badges

answered Aug 22, 2014 at 11:52

John Powell

12.6k6 gold badges64 silver badges69 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

c3cris Over a year ago

As for the inner join, I thought that as well. But when I ran it it matched 32k in a few seconds. After 30 seconds with a left join it only matched 3 rows. So my guess it that it is optimized for inner join at least.

John Powell Over a year ago

Have you tried using force index at all? I don't use MySQL very much these days, but I remember occasionally being able to force the planner to use an index in this way with subqueries?

c3cris Over a year ago

Yes I have tried, and also please check the question for an update for you

John Powell Over a year ago

OK. Travelling today, but going to download latest MySQL and have a good look. Give me a few days.

Collectives™ on Stack Overflow

MYSQL Using Spatial Index

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related