Use eager loading to generate the Currently Online list

[Feld0]

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.

[Marcher Technologies]

isn't that what this kb is for? :unsure:

[Feld0]

isn't that what this kb is for? :unsure:

Looks like it. :O Thanks for the link, Marcher - I was unaware that the fix existed. Down to just 8 queries now. :D