Optimising complex queries: piggyback in-built or write from scratch
I’m looking for some guiding principles here about the best way to deal with some relatively complex queries.
I have a location-based site running WP and BP (latest of each) that geo-tags everything as it is created (users, activity, some CPTs etc.)
On the front end, when a user browses the members directory, for example, they see only the other users within an x-mile radius (there is a slider for x).
Similarly, activity status updates, they see only those updates within a given radius.
At the moment I construct a custom $sql query which includes testing the lat & lng I have stored as extra xprofile fields or fields in wp_bp_activity_meta plus one or two other filters.
This works, but I don’t know whether it is optimal, or scaleable.
An alternative would be to do it in 2 steps: to use the normal bp loop functions to return all potential results regardless of location, and then write a PHP function to apply the radius test.
Would the database be more efficient at running that test or does it choke on complex queries and it would be beneficial to break it up? The core BP functions are presumably highly optimised already.
Doing it in 2 steps would mean there was some scope for caching the first set of results, doing it all in 1 rules out caching as every query will be different, barring someone refreshing the page.
I’m asking this now because I’m about to dip my toes into adding Groups, with similar location-awareness. It shouldn’t be too difficult to replicate what I’ve done with Members and Activity, so that users only see Groups within a certain radius. But where it might get tricky is I’m contemplating going more granular. That is to say, having some universal groups and/or forums where, for example, individual forum posts are geo-tagged, so users in one area will see some of the posts and users in another area will see different posts within the same group/forum.
I imagine the queries might get a bit hairy, and hence the question about whether to continue with custom queries or to use the BP loops as a starting point for a function which then applies the location radius test.
- The topic ‘Optimising complex queries: piggyback in-built or write from scratch’ is closed to new replies.