Search code examples
mysqlsqlindexingspatial

MySQL 5.7 cannot get spatial index to be used

CREATE TABLE zip_polygons_new(
    zipcode MEDIUMINT(5) UNSIGNED NOT NULL,
    zip_polygon MULTIPOLYGON NOT NULL,
    spatial index (zip_polygon)
);

EXPLAIN 
SELECT zipcode
FROM zip_polygons_new zp
WHERE ST_CONTAINS(zp.zip_polygon, ST_GEOMFROMTEXT('POINT(-99.24012 19.53285)'));

I always get:

id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE zp ALL 26376 100.0 Using where

I've tried:

  • both InnoDB and MyIsam.
  • Several spacial functions (e.g. MBRCONTAINS, ST_CONTAINS).
  • putting an equality to 1 on RHS of WHERE clause.

Already checked: MySQL documentation Mysql Spatial index unused Why Spatial index not used by MySQL?

What am I missing to be able to use the index?

Solution

  • As it turns out I did not realized I had mysql@8 running, when switching back to mysql@5.7 the index is now used.

    So the issue was on mysql@8, as my question was related to mysql@5.7 I consider it self-answered. Probably the issue is that mysql@8 expects an explicit SRID to use the spatial index while mysql@5.7 does not.