0

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


2
  • 1
    Provide the structure as comlete CREATE TABLE script. Also specify the approximate rows amounts: total, matched ST_Contains only, matched all another conditions only, matched the whole query. Commented Jul 5, 2022 at 10:03
  • And please provide EXPLAIN SELECT ... Commented Jul 5, 2022 at 22:20

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.