MySQL 5.7 cannot get spatial index to be used

Question

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?

Enrique Vargas · Accepted Answer · 2021-05-22 20:58:13Z

0

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

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

answered May 22, 2021 at 20:58

Enrique Vargas

1133 bronze badges

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

Collectives™ on Stack Overflow

MySQL 5.7 cannot get spatial index to be used

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related