Jump to content

Community

Use eager loading to generate the Currently Online list


Feld0
 Share

Recommended Posts

In its current design, IP.Board runs the following query once for every single member in the Currently Online list (replacing the "3479" at the end with the ID of each active member, of course):

SELECT m.*, m.member_id as my_member_id,p.*,pp.*,g.*,ccb.cache_content FROM members m LEFT JOIN pfields_content p ON ( p.member_id=m.member_id ) LEFT JOIN profile_portal pp ON ( pp.pp_member_id=m.member_id ) LEFT JOIN groups g ON ( g.g_id=m.member_group_id ) LEFT JOIN content_cache_sigs ccb ON ( ccb.cache_content_id=m.member_id ) WHERE m.member_id=3479

It would be sensible to refactor the feature to use eager loading to grab all the members in a single query, no matter how many of them there are:

SELECT m.*, m.member_id as my_member_id,p.*,pp.*,g.*,ccb.cache_content FROM members m LEFT JOIN pfields_content p ON ( p.member_id=m.member_id ) LEFT JOIN profile_portal pp ON ( pp.pp_member_id=m.member_id ) LEFT JOIN groups g ON ( g.g_id=m.member_group_id ) LEFT JOIN content_cache_sigs ccb ON ( ccb.cache_content_id=m.member_id ) WHERE m.member_id IN (3479, 5654, 4543, 6576, 8695, ...)

The online list alone is responsible for adding well over a hundred queries to our index. Disabling it made it come up a good half-second faster. All these queries add a great deal more traffic and overhead to the database connection than there needs to be.

Link to comment
Share on other sites

 Share

  • Recently Browsing   0 members

    No registered users viewing this page.

×
×
  • Create New...

Important Information

We use technologies, such as cookies, to customise content and advertising, to provide social media features and to analyse traffic to the site. We also share information about your use of our site with our trusted social media, advertising and analytics partners. See more about cookies and our Privacy Policy