Tag SQL
This is a shot from the hip at tag selection SQL.
select distinct items.item_id, taggers.login_name
from applied_tags as at
join items using (item_id)
join tags on tags.tag_id = at.tag_id
join taggers on taggers.tagger_id = at.tagger_id
where taggers.tagger_id = 1
and 2 = (select count(*) from applied_tags
join tags using (tag_id)
where label in ('hello', 'world')
and at.item_id = applied_tags.item_id
and at.tagger_id = applied_tags.tagger_id)
It works but it scares me because I think I see table scans.
Ways to improve:
- One could limit the outer query by matching one of the desired tags.
- One could count the number of instances of each tag, and match the outer
query against the least frequent instance. - One could used a series of exist clauses, since they will short circuit.
- One Could start over with a bunch of joins (but the above is pretty easy to read).
Need to find out how to view a query plan in MySQL.