I have a two tables: one with trackings points and one with shops. I want the trackings points that have 1 or more shops within a certain distance from that point.
What I have now:
SELECT
a.*,
b.*
FROM trackings_process a, shops b
WHERE st_distance_sphere(a.coords_point, b.point) < a.coords_accuracy + 30
ORDER BY b.name, a.id
The distance is based on the accuracy of that tracking point. My tracking points table contains 16.000+ records and my shops table has 8.000+ records. The query above takes 71s to run.
My questions: is this the best approach? Strategies to lower the execution time?
