We use mysql 5.7 to store geospatial data. A table contains 5 columns store_id (String), tenant_id (String), type (enum), status(enum), boundaries (Multipolygon). The boundaries column has only one polygon but the type was set as MultiPolygon.
Our query has
SELECT DISTINCT store_id
FROM ${boundariesTable}
WHERE tenant_id = '${tenantId}'
AND status = '${status}'
AND type <> 'Z'
AND ST_Contains(boundaries, GeomFromText(
'Point(${coords.lng} ${coords.lat})'))
This DB call is very slow when boundary data has circles with several geolocation points. Hence, we want to use a geospatial index for the boundaries key. Would we need to modify the above query to use a geospatial index for the boundaries column? If yes then how should we structure the query? Without other parameters like type and tenantId, the number of rows increases multifold. So I am apprehensive to remove all other constraints and retain only the ST_Contains part of the query.
Thank
EXPLAIN SELECT ...