Let there are two tables: "parent" and "child" related by 1:n relation (to every "parent" row there correspond any number of "child" rows).
Suppose we need to fetch a subset of parent table with all corresponding childs.
I can devise three methods to do it:
Prepare two SELECT handles: one for parent and one for child. Do M+1 SELECT queries (where M is the number of relevant "parent" rows), with one query for the parent and M queries for the child with parent row ID being a bind parameter. This variant requires too much queries and thus may be inefficient.
Make one SELECT query with
JOIN. This would fetch parent data repeatedly, even though it is needed just once.
Make first SELECT query for the parent. Then make SELECT query for the child with
ParentID IN (...)WHERE condition based on the parent IDs fetched in the first SELECT. If there are too many parent rows, this second query may overflow query buffer size.
Please describe advantages and disadvantages of every three methods. Which method to use in practice? (The primary criterion is performance.) I'd stick with the third method (almost always), but want to hear about its possible disadvantages.
Note that we use MySQL.