Skip to:
Content
Pages
Categories
Search
Top
Bottom

Optimising complex queries: piggyback in-built or write from scratch


  • terraling
    Participant

    @terraling

    10 years, 1 month ago

    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.

    Phew. Thanks.

Viewing 5 replies - 1 through 5 (of 5 total)

  • shanebp
    Moderator

    @shanebp

    10 years, 1 month ago

    >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.

    Don’t do that.

    Use the radius, etc test first to gather matching user ids.
    Then pass them to BP_User_Query, using the bp_pre_user_query_construct hook, so that you only have BP process the relevant ids.

    BP_User_Query


    Boone Gorges
    Keymaster

    @boonebgorges

    10 years, 1 month ago

    I agree with @shanebp.

    If you go with this technique, it’s likely that the scaling bottleneck in the future will be your distance queries against the xprofile_data table. From what I’m guessing, you’re (a) having MySQL cast the string values in that table to floats so that you can do radius math on them, and (b) possibly joining the table against itself to get lat + long. In the future, you might consider creating a custom table for this purpose, which would either replace or mirror the storage of lat/long in the xprofile_data table. Then, do your distance queries against that custom table.

    But yeah, what @shanebp said 🙂 Definitely do two queries, and cache the heck out of that first one, if possible.


    terraling
    Participant

    @terraling

    10 years, 1 month ago

    OK, great, thank you. That’s enough to set me off on the right track.

    Currently what gets submitted for the members directory looks something like this (actual query), and it ain’t too pretty:

    SELECT u.ID, xpd1.value as UserLat, xpd2.value as UserLon, ( acos(sin(0.632743928228) * sin( RADIANS(xpd1.value) ) + cos(0.632743928228) * cos( RADIANS(xpd1.value ) ) * cos(-0.104081294481 - RADIANS(xpd2.value ) )) * 6371 ) as distance 
FROM wp_users u JOIN wp_bp_xprofile_data xpd1
ON xpd1.user_id = u.ID JOIN wp_bp_xprofile_data xpd2
ON xpd2.user_id = u.ID 
WHERE xpd1.field_id = 15 
AND xpd2.field_id = 16 
AND ( RADIANS(xpd1.value ) > 0.624895866699
AND RADIANS(xpd1.value ) < 0.640591989757)
AND ( RADIANS(xpd2.value ) > -0.113813472053
AND RADIANS(xpd2.value ) < -0.0943491169082) 
AND acos(sin(0.632743928228) * sin( RADIANS(xpd1.value ) ) + cos(0.632743928228) * cos( RADIANS(xpd1.value ) ) * cos( RADIANS(xpd2.value ) - -0.104081294481)) <= 0.0078480615288 
ORDER BY distance ASC

    shanebp
    Moderator

    @shanebp

    10 years, 1 month ago

    I’d use the approach @boonebgorges suggests – a custom table with these fields:
    -user_id unique
    -long
    -lat

    Then you don’t need any joins.

    >individual forum posts are geo-tagged

    Based on where they were created or based on the existing geo-tag for the user?
    If the former, you can add a column to the custom table.
    If the latter, you’re good to go.


    terraling
    Participant

    @terraling

    10 years, 1 month ago

    Good question and the answer is it depends on the specific case. Some content is tied to its physical location (i.e. if the user moved the content would stay put), in other cases the location context is determined by the user (i.e. if the user moves the content goes with them).

    So I may need a bit of both.

    Thanks again, very helpful.

Viewing 5 replies - 1 through 5 (of 5 total)
  • The topic ‘Optimising complex queries: piggyback in-built or write from scratch’ is closed to new replies.

Topic Info

Topic Tags

Skip to toolbar