What's the best way to display the number of items related to tags?

Question

Hello,

After following this recommendation regarding tags tables structure, I've implemented the following design:

Table: Item
Columns: ItemID, Title, Content

Table: Tag
Columns: TagID, Title

Table: ItemTag
Columns: ItemID, TagID

I'm facing another problem, I wanna display the number each tag appears in the system. What's the best way to do this in terms of search query / php code that will not cause a severe performance impact (tags count will appear for each item).

Thanks,

Roy

Answer 1

select count(*)
from ItemTag
where TagId = X

is mySQL that will get it dynamically.

If you're worried about the performance hit, you should store a count in the tag table, and increment/decrement it when you tag or untag an item.

Answer 2

Pretty sure the following (SQL) will get all the tag titles and associated counts of how often they appear on items.

select tag.title, count(itemtag.tagid) 
from tag, itemtag
where itemtag.tagid = tag.tagid
group by tag.title

Answer 3

SELECT Tag.Title, COUNT(Tag.TagID) AS TagCount
FROM ItemTag
LEFT JOIN Tag ON
(ItemTag.TagID = Tag.TagID)
GROUP BY Tag.Title
ORDER BY Tag.Title

Gives you a list of tags (ni alphabetical order) followed by the number of times they are used against an item. Unused tags do not appear.