CODEWITHSUNDEEP
mysqlmariadb
Pocifik
Pocifik

Reputation: 83

Slow performance with ST_CONTAINS

I have simple sql query with JOIN which connected if point in multipolygon:

SELECT `oktmo_geometry`.`oktmo` FROM `place`
INNER JOIN `oktmo_geometry` ON ST_CONTAINS(`oktmo_geometry`.`geometry`, Point(`place`.lng, `place`.lat))

But that query very slow. I have 12k places and 300 geometry. For only 50 places it take 3-5 sec (depending on the version MySQL). I had tried use MariaDB, and that was ~50 times faster than MySQL, but still slow i think.

Maybe something wrong with my query or anything else?

oktmo_geometry.geometry is multipolygon type.

Upvotes: 2

Views: 2230

Answers (1)

user3666385
user3666385

Reputation: 59

A good portion of your speed is lost here: Point(`place`.lng, `place`.lat)

This can't be indexed, so in principle it has to go through every `place` row to complete the search.

A better solution would be to create a new column like `place`.pointvalue that is a point data type derived from Point(`place`.lng, `place`.lat), and then index `place`.pointvalue.

Then your query would look like

SELECT `mo_geometry`.`oktmo` FROM `place`
INNER JOIN `oktmo_geometry` ON ST_CONTAINS(`oktmo_geometry`.`geometry`, `place`.pointvalue)

I suspect that will go a lot faster.

Upvotes: 2

Related Questions