Results 1 to 7 of 7
Apr 29, 2013, 03:41 #1
- Join Date
- Apr 2013
- 0 Post(s)
- 0 Thread(s)
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:
SELECT various_columns_from_table_"photos"_and_some_columns_from_table_"people" FROM captions INNER JOIN photos ON captions.id = photos.captionid LEFT JOIN people ON photos.photografph_id = people.id WHERE MATCH(caption) AGAINST(+'hometown') UNION SELECT various_columns_from_table_"photos"_and_some_columns_from_table_"people" FROM photos 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 WHERE 'hometown' IN(various_columns_in_the_tables_"photos"_"people"_and_"keywords") 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.