如何避免在循环内调用子数据的数据库查询

Question

I've been struggling with trying to figure out what is the best, most efficient way to handle displaying child data. In my specific case I'm using PHP and MySQL, but I feel that this is more of a "generally in any language" sort of deal.

My two thoughts are (for this example I'll be listing invoices and their line items)

1. Joining the child data (invoice items) to the main data (invoices) as to only have a single query My problem with this is that, say I have 500 line items on an invoice (probably not realistic, but things happen), then I would have sent 500 times the overall invoice data from the MySQL server to my PHP script and that just sounds ridiculous since I only need it the once time.

2. And the second option would be to, while looping through the invoices and displaying the overall invoice data, select the invoices's line items. And this, of course, is now contacting the database 500 more times.

Are there any other options for dealing with this data that makes logical sense (with the given schema)? I'm almost 100% sure there are, since I can't believe that I'm the first person to think about this issue, but I think I'm just having difficulty finding the right way to search for more information on this topic.

Answer 1

Joining the child data (invoice items) to the main data (invoices) as to only have a single query

That's the conventional way of handling this requirement. It does, indeed, handle redundant data, and there is some overhead.

But.

1. that's the reason it's possible to specify a compressed connection to a RDBMS from a client ... compression mitigates the network overhead of the redundant data.
2. the redundant data in a single result set costs much less than the repeated queries.

Most folks just retrieve the redundant data in this kind of application. Program products like Crystal Reports do this.

If it just won't work for you, you retrieve and save one result set for your master records ... maybe something like this.

   SELECT master_id, name, address, whatever
     FROM master m
    WHERE m.whatever = whatever
    ORDER BY whatever

Then, put those into an associative array by master_id.

Then, retrieve the detail records.

  SELECT d.master_id, d.detail_id, d.item, d.unit, d.quantity
    FROM detail d
    JOIN master m ON d.master_id = m.master_id
   WHERE m.whatever = whatever
   ORDER BY d.master_id, d.detail_id, whatever

You'll get a result set with all your relevant detail records (invoice items) tagged with the master_id value. You can them match them up in your php program to the master records. You're basically doing the join in your application code.

If all that sounds like too much of a pain in the neck... just go for the redundant data and get your project done. You can always optimize later if you must.