How to make eager loading?

Question

How can I make eager loading between 1 primary key table and 2 foreign key tables?

say I have Table A (Primary)

Table A Id INT Name

Table B Id INT aID INT

Table C Id INT aID INT

Answer 1

This is too long for a comment.

I think all you need to do is to write a single query on all the tables:

select . ..
from a join
     b
     on b.aid = a.id join
     c
     on c.aid = a.id;

The database engine then manages the storage and can intelligently load pages into the page cache and keep them cached for future queries.

If you try to split this into multiple queries, then I don't see a way that one query on a would result in the corresponding rows of b being loaded. On the other hand, how large are the tables? If they are not large, the tables will be loaded into the page cache entirely and you won't have to worry about individual rows.

The one issue is that an initial query would be on a cold cache and take longer than subsequent queries.