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!
MySql 'intersect' query?
Moderator: General Moderators
this may help you....http://www.weberdev.com/get_example.php3?count=3855...its very adaptable to what you need
Code: Select all
mysql> select * from images;
+----+------+
| id | url |
+----+------+
| 1 | asd |
| 2 | sdf |
| 3 | dfg |
+----+------+
3 rows in set (0.00 sec)
mysql> select * from keywords;
+----+------+
| id | text |
+----+------+
| 1 | key1 |
| 2 | key2 |
| 3 | key3 |
+----+------+
3 rows in set (0.00 sec)
mysql> select * from images_keywords;
+------------+----------+
| keyword_id | image_id |
+------------+----------+
| 1 | 1 |
| 1 | 2 |
| 3 | 1 |
+------------+----------+
3 rows in set (0.00 sec)
mysql> select * from images inner join images_keywords on images.id = images_keywords.image_id
inner join keywords on keywords.id=images_keywords.keyword_id group by images.id having
sum(text='key1')+sum(text='key3')=2;
+----+------+------------+----------+----+------+
| id | url | keyword_id | image_id | id | text |
+----+------+------------+----------+----+------+
| 1 | asd | 1 | 1 | 1 | key1 |
+----+------+------------+----------+----+------+
1 row in set (0.00 sec)Benchmarking is up to you
If you can get your list of keywords into a comma seperated list e.g.
$keywords = '5,2,18';
Then you could simply use the IN syntax...
$keywords = '5,2,18';
Then you could simply use the IN syntax...
Code: Select all
SELECT `photo-id` FROM `photos` WHERE `keyword-id` IN ($keywords) GROUP BY `photo-id`;Your approach implies OR boolean operator being used between keywords. gark's question implied AND.redmonkey wrote:If you can get your list of keywords into a comma seperated list e.g.
$keywords = '5,2,18';
Then you could simply use the IN syntax...Code: Select all
SELECT `photo-id` FROM `photos` WHERE `keyword-id` IN ($keywords) GROUP BY `photo-id`;