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

Answer 1

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.