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:

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

  2. Make one SELECT query with JOIN. This would fetch parent data repeatedly, even though it is needed just once.

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

  • The first variant however has the advantage that we don't need to load all data into memory. – porton Oct 6 '15 at 17:05
  • 1
    4. Make select for parents, then make select where you join parents as in 2. but only select the fields from children. That way you "send" the conditions instead of the parentIDs so no long query, but there is no big duplication of parents data. Usually the drawbacks of method 2 aren't so big unless you select lots of columns from parent and only a couple from child. – jkavalik Oct 6 '15 at 18:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.