Description
Apologies in advance if that's not the place to ask, but I'm just trying to have a better understanding MySQL (8.0) spatial features, specifically indexing of POINT column types and using the provided eloquent scope methods to trigger the index.
In a test project that I've setup according to the README of this package, I've seeded ~200k records with an indexed point column and I'm trying to have an eloquent pagination on results served by Response::json:
The JSON response returns a total value, which is re-calculated with every request and is kinda slow.
The slow query is 500+ ms and looks like this:
select count(*) as aggregate from `table` where st_distance_sphere(`location`, ST_GeomFromText('POINT(-1 1)')) <= 100000
If I add an order to this query, the query time doubles.
After doing a bit of manual queries, I got to the point that I've noticed that many of the ST_* methods does not utilize the column index, I tried using st_distance, st_distance_sphere, st_within, etc., all give results around 500 ms - 1 sec, and it doubles with ordering.
Is there a way to optimize these queries to use the index? Is there a "best practices" guide or something I can take a look at?
Thank you.