Query for searching in multiple tables – UNION or not?
I'm trying to create a query in order to search for a specific value in several different tables. I have a table of photos, another one with keywords and a third with people (both photographers and people in the pictures), and finally one where I keep the image captions with a full text index. The fastest solution I've come up with so far is this one:
Does anyone see any obvious room for improvement or any errors I should correct? I'm not sure about all the LEFT JOINS, but it seems to be working as intended.
INNER JOIN photos
ON captions.id = photos.captionid
LEFT JOIN people ON photos.photografph_id = people.id
WHERE MATCH(caption) AGAINST(+'hometown')
LEFT JOIN people AS photographers ON photos.photografph_id = photographers.id
LEFT JOIN keywords_photos as kp
ON kp.photoid = photos.id
LEFT JOIN keywords AS k
ON k.id = kp.wordid
LEFT JOIN people_photos AS pp
ON photos.id = pp.photoid
LEFT JOIN people as people
ON pp.personid = people.id
GROUP BY photos.id
I've tried to join all the tables together in a single SELECT statement, but all my attempts so far has resulted in searches that take several seconds.