title: The most efficient MySQL query to get n models with m child models?
body: <p>I have a many to many relationship of <code>photos</code> to <code>categories</code> via <code>lightboxes</code>.</p>
  <p><code>photos</code> to <code>lightboxes</code> is many to many, <code>categories</code> to <code>lightboxes</code> is one to one.</p>
  <p>The <code>photos</code> and <code>lightboxaphotos</code> pivot table both have millions of records.</p>
  <p>What I need to do is for each category, which has a lightbox id, get the first three photos to show as a preview.</p>
  <p>To do this in one query, I am currently doing this with <code>UNION</code>, like so:</p>
  <pre class="lang-sql prettyprint-override"><code>(
  SELECT
          photo_id,
          photo_name,
          category_name,
          category_id,
          category_parent_id,
          lightbox_id
      FROM
          photos
      LEFT JOIN lightboxphotos ON photo_id = lightboxphoto_photo_id
      LEFT JOIN categories ON lightboxphoto_lightbox_id = lightbox_id
  WHERE
      lightboxphoto_lightbox_id = 123
  ORDER BY
      lightboxphoto_sortorder ASC
  LIMIT 3)
  UNION (
      SELECT
          photo_id,
          photo_name,
          category_name,
          category_id,
          category_parent_id,
          lightbox_id
      FROM
          photos
      LEFT JOIN lightboxphotos ON photo_id = lightboxphoto_photo_id
      LEFT JOIN categories ON lightboxphoto_lightbox_id = lightbox_id
  WHERE
      lightboxphoto_lightbox_id = 345
  ORDER BY
      lightboxphoto_sortorder ASC
  LIMIT 3)
  UNION 
  -- ... etc for each category
  </code></pre>
  <p>Currently this takes about 1.8 seconds to run for 10 categories. The number of categories may hit the 100+ mark. I will use caching, but what I'm most interested in in the first place is using the most efficient query. How do I make this more efficient?</p>
  <p>I don't know much about raw MySQL, as usually I would let Laravel handle this, but I'm on a legacy project that doesn't use Laravel.</p>