Re: join with redundant results VS simpler join plus multiple selects

On Thu, Nov 20, 2008 at 20:40, Craig Ringer <craig(at)postnewspapers(dot)com(dot)au> wrote:
> I'm not even sure how you'd achieve that (exactly 3 randomly selected
> images per user) in with a single query. Then again, it's stupidly late
> here, so my brain may not be working. Any chance you can post a query
> that shows what you're doing?

Basically the big join would go like this:

SELECT * FROM gall2ppl gp
JOIN people p ON (gp.gallery_id=N AND gp.gallery_id=p.gallery_id)
JOIN gall2ppl2img gpi ON (gp.gp_id=gpi.gp_id)
JOIN images i ON (gpi.image_id=i.image_id)

Where gp links galleries to people, images are images, and
gall2ppl2img links images to the links between galleries and people.
As you'll notice, I have a gallery_id=N condition, which means I work
with one gallery at a time.

The second scenario would mean doing just the first JOIN, then cycling
through the results and doing the last JOIN for each person.

Getting 3 random images is achieved by slapping an ORDER BY random()
LIMIT 3 at the end of the last JOIN.

I think this means that, although the big join would be more
efficient, it's not practical. Because I also need to limit the number
of persons to get pagination (means using LIMIT and OFFSET on the
people set) and I don't see how I can do that simultaneously (limit
both the people set and the image set in the same join). Not to
mention the added headache of ordering first by some attribute of the
people (name or date of join) then by the images with the random()
thing. The mixed ordering could probably be done but I really dunno
about the simultaneous LIMITing. And in achieving this the big join
may well lose its initial efficiency.

Would it be better if I took the first join and made a view out of it,
then SELECT on that to get the images? The possible advantage would be
I wouldn't have to do the last JOIN for the images, it would be all
straight SELECT's.