Page 1 of 1

MySql 'intersect' query?

Posted: Tue Jul 06, 2004 12:06 pm
by gark
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!

Posted: Tue Jul 06, 2004 1:57 pm
by lostboy
this may help you....http://www.weberdev.com/get_example.php3?count=3855...its very adaptable to what you need

Posted: Tue Jul 06, 2004 2:47 pm
by Weirdan

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)
The having sum(text='key1')+sum(text='key3')=2 is accurate because there's composite primary key on both columns in images_keywords table.

Benchmarking is up to you ;)

Posted: Tue Jul 06, 2004 2:57 pm
by redmonkey
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`;

Posted: Tue Jul 06, 2004 3:30 pm
by Weirdan
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`;
Your approach implies OR boolean operator being used between keywords. gark's question implied AND.

Posted: Tue Jul 06, 2004 3:32 pm
by lostboy
[edit=Weirdan] Bastien, link to external tutorial was enough, no need to post entire code here [/edit]

Posted: Tue Jul 06, 2004 3:49 pm
by gark
I'm new to PHP and MySql, so I'll have to study these examples a bit, but I'm sure I'll get it to work. Thanks a lot to all of you for your (quick!) help!

Posted: Wed Jul 07, 2004 7:00 pm
by redmonkey
Weirdan wrote: Your approach implies OR boolean operator being used between keywords. gark's question implied AND.
Very true, my mistake!