MySql 'intersect' query?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
gark
Forum Newbie
Posts: 2
Joined: Tue Jul 06, 2004 12:06 pm

MySql 'intersect' query?

Post 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!
lostboy
Forum Contributor
Posts: 329
Joined: Mon Dec 30, 2002 8:12 pm
Location: toronto,canada

Post by lostboy »

this may help you....http://www.weberdev.com/get_example.php3?count=3855...its very adaptable to what you need
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post 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 ;)
redmonkey
Forum Regular
Posts: 836
Joined: Thu Dec 18, 2003 3:58 pm

Post 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`;
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post 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.
lostboy
Forum Contributor
Posts: 329
Joined: Mon Dec 30, 2002 8:12 pm
Location: toronto,canada

Post by lostboy »

[edit=Weirdan] Bastien, link to external tutorial was enough, no need to post entire code here [/edit]
gark
Forum Newbie
Posts: 2
Joined: Tue Jul 06, 2004 12:06 pm

Post 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!
redmonkey
Forum Regular
Posts: 836
Joined: Thu Dec 18, 2003 3:58 pm

Post by redmonkey »

Weirdan wrote: Your approach implies OR boolean operator being used between keywords. gark's question implied AND.
Very true, my mistake!
Post Reply