Spatial Indexes in Production: MySQL 8 InnoDB R-Trees vs PostGIS GiST
The store-locator endpoint had a spatial index on both engines and was doing a full scan on both — because ST_Distance_Sphere in a WHERE clause uses no index anywhere. Then a bulk load died at row 840,000 with ERROR 3643 over an SRID mismatch. Field notes on geo predicates that actually use the index.
The “stores within 5 km” endpoint was slow on two different databases for the same reason. On the MySQL 8.0 system, 2.3 million store rows, a perfectly good SPATIAL INDEX on the location column, and a WHERE ST_Distance_Sphere(location, point) < 5000 clause that scanned the entire table at 380ms p95. On the PostgreSQL 15 plus PostGIS 3.4 system it was migrated to, a GiST index on the geometry column, and WHERE ST_Distance(geom, point) < 5000 doing the identical full scan. Both teams had created the index, run the query, seen it stay slow, and concluded that spatial indexes were broken. Spatial indexes were not broken; distance is just not an indexable predicate on either engine, and the rewrite that fixes it is different on each.
The second incident that quarter was a bulk load: 840,000 rows in, MySQL rejected an insert with ERROR 3643 (HY000): The SRID of the geometry does not match the SRID of the column, because the loader built geometries with ST_GeomFromText(wkt) — SRID 0 by default — into a column declared SRID 4326. Spatial data carries a coordinate-system contract that regular columns do not, and both engines enforce it at the worst possible time. This article is the production comparison: which predicates use the index, how SRIDs break loads, and how the two index families differ.
Why doesn’t a distance filter use the spatial index?
Because a spatial index organizes bounding boxes for containment and intersection tests, and a raw distance comparison cannot be answered from a bounding box — so on both engines you must give the planner a bbox predicate it can index, with the exact distance as a refinement. On MySQL 8 the indexable predicates are the ST_ and MBR_ containment and intersection functions with a constant geometry argument; on PostGIS the indexable operator is && (bounding-box overlap), and the idiomatic radius query is ST_DWithin, which the planner expands into && plus an exact distance check:
-- MySQL 8.0: envelope the search circle, then refine
SELECT id, name,
ST_Distance_Sphere(location, ST_GeomFromText('POINT(32.78 39.87)', 4326)) AS dist_m
FROM stores
WHERE MBRContains(
ST_Buffer(ST_GeomFromText('POINT(32.78 39.87)', 4326), 0.05),
location)
AND ST_Distance_Sphere(location, ST_GeomFromText('POINT(32.78 39.87)', 4326)) < 5000;
-- PostGIS: ST_DWithin does the bbox-plus-refine expansion for you
SELECT id, name, ST_Distance(geom, ST_MakePoint(32.78, 39.87)::geography) AS dist_m
FROM stores
WHERE ST_DWithin(geom, ST_MakePoint(32.78, 39.87)::geography, 5000);After the rewrites, both endpoints dropped under 15ms. Note the degree-versus-meter trap baked into the examples: MySQL’s ST_Buffer on SRID 4326 works in degrees, so 0.05 is a rough five-kilometer fudge at that latitude, while casting to PostGIS geography makes ST_DWithin genuinely metric. Approximation in the indexable prefilter is fine — the exact predicate refines it — but you must know which unit your envelope is in, or your “5 km” radius quietly becomes 4 km or 8 km depending on latitude.
What breaks when SRIDs do not match?
Everything that loads, inserts, or compares, and it breaks at runtime with the row in hand, not at schema design time. MySQL 8 enforces the column’s SRID attribute: a geometry with a different SRID is rejected with ERROR 3643, and an indexable column effectively requires the SRID attribute at creation. The loader fix is one function call — ST_GeomFromText(wkt, 4326) — but finding it took an afternoon because the first 840,000 rows happened to come from a file that already carried the right SRID. PostgreSQL’s geometry(POINT, 4326) typmod rejects mismatches just as firmly — “Geometry SRID (0) does not match column SRID (4326)” — and the loader-side fix is ST_SetSRID.
The subtler SRID problem is mixing 4326 with projected systems: on PostGIS, ST_Transform converts between them and ST_Distance on geography gives meters anywhere on earth; on MySQL 8, ST_Distance_Sphere assumes a sphere and geographic SRIDs, and operations across different SRIDs error rather than transform. The discipline that survives both: one SRID per column, declared in the schema, set explicitly at every load boundary, and geography-vs-geometry decided once per use case — meters and small areas favor PostGIS geography or MySQL ST_Distance_Sphere; projected precision work favors PostGIS geometry with a chosen projected SRID.
How do the index families themselves differ?
MySQL 8 InnoDB uses an R-tree exposed through the SPATIAL INDEX keyword; PostGIS uses a GiST index that exposes bounding-box operators to the whole planner, and that architectural difference buys PostGIS two things MySQL does not have. First, nearest-neighbor search: PostGIS’s <-> KNN operator returns the k closest stores in index order, no radius guesswork at all, while MySQL has no KNN spatial operator and you fake it with a growing envelope. Second, operator composability: && composes with partitioning, expression indexes, and plain boolean logic anywhere in the query, where MySQL’s index use depends on the optimizer recognizing specific function shapes — which is why the MySQL prefilter above is written in the exact form the optimizer expects. MySQL does have real strengths to name honestly: spatial columns must be NOT NULL for indexing (attempting a spatial index on a nullable column fails with ERROR 1252: All parts of a SPATIAL index must be NOT NULL), which forces the data-quality contract early, and for straightforward containment lookups at moderate scale the R-tree performs fine.
Verify index use rather than assume it, on both sides:
-- MySQL: look for a range/ref access on the spatial index, not ALL
EXPLAIN SELECT id FROM stores
WHERE MBRContains(ST_Buffer(ST_GeomFromText('POINT(32.78 39.87)', 4326), 0.05), location);
-- PostgreSQL: look for an Index Scan with an && index cond
EXPLAIN (ANALYZE, BUFFERS)
SELECT id FROM stores
WHERE geom && ST_Expand(ST_MakePoint(32.78, 39.87)::geography, 5000)::geometry;The PostGIS side of this craft goes much deeper than a comparison can — the PostGIS geo queries in production notes cover KNN tuning, geometry simplification, and index-only tricks — and when a spatial query refuses the index on either engine, the general plan-reading workflow in MySQL EXPLAIN vs PostgreSQL EXPLAIN ANALYZE is the diagnostic baseline.
Where MonPG fits when geo queries scan
A spatial query that falls off its index looks like any other slow query — rising runtime and buffer reads on one normalized statement — until someone notices the table has a spatial index that nothing uses. MonPG’s PostgreSQL monitoring tracks per-statement runtime and I/O trends, so the day the store table outgrows the plan’s assumptions, the regression shows up as a named query with numbers, not as a support ticket about the map being slow again.
MonPG monitors PostgreSQL today; MySQL support is on the roadmap. When it lands, the MySQL side of this article is what it will surface: full scans on spatially-indexed tables and the digest-level cost of the MBRContains pattern versus the raw-distance anti-pattern. Until then, the MySQL monitoring page tracks that work. The portable lesson is one sentence: a spatial index answers “does this box overlap that box”, and every geo query on either engine has to be rewritten into that question before the index can help.