MySql 'intersect' query?
Posted: Tue Jul 06, 2004 12:06 pm
I'm having a small problem with a MySql query, I have the following database which connects two other databases (keywords and photos):
keyword-id photo-id
-------------------------
5 - 3
2 - 3
1 - 8
27 - 3
18 - 6
The keyword-id points to a keyword (for example 'tree' or 'house), and the photo-id points to a photo. Now if I want to find all photo's which depict a house I can just search for all the records with the keyword-id for 'house'.
The problem arises when I want to search with multiple keywords. Right now I search for all records with a keyword A, after that I do a search for all records with keyword B. When I do a array_intersect with the two results I get all photo's which share keywords A and B.
Is there a way I could do this with one MySql query? When my database gets larger I don't think this method will work. Another drawback is that I cannot retrieve my data in portions, because only after the array_intersect I know how many good results I have.
My reason for doing it this way is because I though it would be much faster than putting the keywords in a textstring. If I would put the keywords in a string and use a Boolean full-text search, would it be fast enough? (I think the database will hold a maximum of 10.000 photo's)
A push in the right direction would be very much appreciated!
keyword-id photo-id
-------------------------
5 - 3
2 - 3
1 - 8
27 - 3
18 - 6
The keyword-id points to a keyword (for example 'tree' or 'house), and the photo-id points to a photo. Now if I want to find all photo's which depict a house I can just search for all the records with the keyword-id for 'house'.
The problem arises when I want to search with multiple keywords. Right now I search for all records with a keyword A, after that I do a search for all records with keyword B. When I do a array_intersect with the two results I get all photo's which share keywords A and B.
Is there a way I could do this with one MySql query? When my database gets larger I don't think this method will work. Another drawback is that I cannot retrieve my data in portions, because only after the array_intersect I know how many good results I have.
My reason for doing it this way is because I though it would be much faster than putting the keywords in a textstring. If I would put the keywords in a string and use a Boolean full-text search, would it be fast enough? (I think the database will hold a maximum of 10.000 photo's)
A push in the right direction would be very much appreciated!