> ORDER BY time DESC LIMIT ?
> Hmmm... Yes, that would probably put a wrinkle in my algorithm.
Well, I'll take that back...
PARTITION BY RANGE (lat) ... -- Note 1
PRIMARY KEY (lon, uid, lat); -- Note 2
SELECT g.*,
ACOS(SIN(?) * SIN(lat) + COS(?) * COS(lat) * COS(lon - ?)) * @R as radius
FROM g
WHERE (g.lat BETWEEN ? and ? -- Note 3 (pruning)
AND g.lon BETWEEN ? and ?) -- Note 4 (using PK)
AND (g....)
HAVING radius BETWEEN ? and ? -- Note 5
ORDER BY g.time DESC -- Note 5
LIMIT ? -- Note 5
Note 1: Make 20-40 partitions by latitude.
Note 2: `lon` first in the PK so that a minimum number of rows need to be scanned; uid included to make the PK unique; lat required because it is the 'partition key'. (lon must be first; the order of the other two won't matter.)
Note 3: "lat BETWEEN" causes "partition pruning" to be useful. Only one (or a few) partitions will need to be looked in.
Note 4: "lon BETWEEN" makes good use of the PK because `lon` is first.
Note 5: These kick in too late for the PK, etc to care.
Note: I left out any references to userstable.
Now to add userstable back in...
SELECT x.*, u.user_id
FROM ( the-SELECT-above ) AS x
JOIN u ON x.uid = u.uid
ORDER BY x.time DESC; -- Note 6
Note 6: The extra ORDER BY may be necessary.
I am unclear on whether SQL_CALC_FOUND_ROWS can be added back in. And if so, whether it would need to be in the inner or the outer query.
Hmmmm... This _could_ be even better:
SELECT SQL_CALC_FOUND_ROWS -- Note 7
g.*,
ACOS(SIN(?) * SIN(lat) + COS(?) * COS(lat) * COS(lon - ?)) * @R as radius,
( SELECT user_id FROM userstable WHERE uid = g.uid ) AS user_id -- Note 8
FROM g
WHERE (g.lat BETWEEN ? and ?
AND g.lon BETWEEN ? and ?)
AND (g....)
HAVING radius BETWEEN ? and ?
ORDER BY g.time DESC -- Note 9
LIMIT ?
Note 7: The CALC should work in this context.
Note 8: Note how I turned things inside out to get user_id. The drawback with this approach is that it must do the subquery more times -- before restricting on HAVING and LIMIT. So, performance will depend on whether this is significant. (I don't have a feel for your data, etc, to make a judgement call.)
Note 9: I believe all my comments in this message apply equally for "ORDER BY radius". I focused on `time` because that is alien to what I had designed for in the link.