I have a spatial index on GEO_LOCATION column, but when I do EXPLAIN it doesn't show that the index is being used. Can anyone tell why?
EXPLAIN
SELECT AsText(GEO_LOCATION)
FROM PERSON
WHERE ST_Distance(POINT(-94.0724223,38.0234332), GEO_LOCATION) <= 10
id: 1
select type: SIMPLE
table: PERSON
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 612602
Extra: Using where
This is my environment:
Server type: MariaDB
Server version: 10.1.8-MariaDB - mariadb.org binary distribution
Protocol version: 10
Server charset: UTF-8 Unicode (utf8)
Apache/2.4.17 (Win32) OpenSSL/1.0.2d PHP/5.6.14
Database client version: libmysql - mysqlnd 5.0.11-dev - 20120503
PHP extension: mysqli Documentation
PHP version: 5.6.14
Unfortunately ST_Distance() < threshold isn't a sargable search criterion. To satisfy this query, MySQL must compute the function's value for every row in the table, and then compare it to the threshold. So it has to do a full table scan (or maybe a full index scan).
To exploit an index to speed up this query, you're going to need a bounding-box criterion. The query is a lot more elaborate but also a lot faster. Assuming your x/y points in your geometry represent latitude/longitude in degrees, that query might look like this:
set @latpoint = 38.0234332;
set @lngpoint = -94.0724223;
set @r = 10.0; /* ten mile radius */
set @units=69.0; /* 69 statute miles per degree */
SELECT AsText(geo)
FROM markers
WHERE MbrContains(GeomFromText(
CONCAT('LINESTRING(', @latpoint-(@r/@units),' ',
@lngpoint-(@r /(@units* COS(RADIANS(@latpoint)))),
',',
@latpoint+(@r/@units) ,' ',
@lngpoint+(@r /(@units * COS(RADIANS(@latpoint)))),
')')),
geo)
How does this work? For one thing, the MbrContains(bound,item) function is sargable. For another thing, the big ugly concat item yields a diagonal line from the southwest to the northeast corner of the bounding rectangle. Using your data point and ten mile radius it looks like this.
LINESTRING(37.8785 -94.2564,38.1684 -93.8884)
When you use the GeomFromText() rendering of that diagonal line in the first argument to MbrContains() it serves as a bounding rectangle. MbrContains() can then exploit the nifty quadtree geometry index.
Thirdly, ST_Distance(), in MySQL, doesn't handle great circle latitude and longitude computations. (PostgreSQL has a more comprehensive GIS extension.) MySQL's is as dumb as a flapjack in flatland. It assumes your points in your geometric objects are represented in planar geometry. So ST_Distance() < 10.0 with lng/lat points does something strange.
There's one flaw in the results this query generates; it returns all the points in the bounding box, not just within the specified radius. That's solvable with a separate distance computation. I've written all this up in some detail here.
Note: For GPS-resolution latitude and longitude, 32-bit FLOAT data has sufficient precision. DOUBLE is what MySQL's geo extension uses. When you're working in degrees, more than five places after the decimal point is beyond the precision of GPS. DECIMAL() is not an ideal datatype for lat/lng coordinates.
