First-time poster so please be gentle :)
I have a rather large table in my database (MySQL in case it matters) that holds lat/lng values of certain points of interest around the world (table name: Maps). The table is very large (millions of rows) and could grow significantly larger since users can add rows to it.
I have another table called Users that also holds lat/lng values of user locations around the world. It is currently in the low thousands, and I wouldn't expect it to grow to more than a few tens of thousands.
My scenario is that I need to run a query that compares the lat/lng position of some users in the Users table against some locations in the Maps table and return locations less than a certain distance. The SQL is as follows:
SELECT x.type FROM Maps x INNER JOIN Ranges ON Ranges.type = x.type WHERE x.user_ID IN ($users) AND m.lat BETWEEN x.lat - Ranges.range AND x.lat + Ranges.range AND m.lng BETWEEN x.lng - Ranges.vision_range * COS(RADIANS(x.lat)) AND x.lng + Ranges.vision_range * COS(RADIANS(x.lat)) AND (6378137 * ACOS(COS(RADIANS(x.lat)) * COS(RADIANS(m.lat)) * COS(RADIANS(m.lng) - RADIANS(x.lng)) + SIN(RADIANS(x.lat)) * SIN(RADIANS(m.lat)))) < Ranges.range
This query is currently in production and works OK. It follows my normal practice (and I'm assuming "best" practice) of running the specific query in SQL to return only the result set we are interested in, a so-called "minimum" or if you like "accurate" result set. I then don't need to further process the result set and can return it to the user's front-end app. A usual query returns maybe 100-1,000 values, however due to the relatively complex mathematical calculations takes 300-600ms to complete. This isn't ideal, because it's a web front end and the lag is noticeable.
However, I've been experimenting with running a "dumb" query that simply uses a large bounding box to return all possible values within a range and then use PHP to do the mathematical processing. The SQL is as follows:
SELECT m.type FROM Maps m WHERE m.user_ID IN ($users) AND m.lat BETWEEN $minLat AND $maxLat AND m.lng BETWEEN $minLng AND $maxLng
This query returns results in 3-6ms! However, the result set is huge and can be tens of thousands of rows. PHP obviously has no issues with the mathematics and can process it in a negligible amount of time.
Is there any reason I should not process the data in PHP? Am I missing something here about the tradeoff between the massive reduction in query time versus the huge volume of data returned (most of which will be "thrown away")?