Question:
I have 3 tables which allows me to get all tags of some picture, or all pictures with a specific tag.
I would like also to know the number of times each tag exist. How could I get this info using MySQL ?
Is it worth to add a "Count" column to the Tags table to hold this info ?
Files Table
File ID File Name ... ------- --------- 1 a.jpg ... 2 b.png ... 3 c.jpg ... . . . . . . Tags Table
Tag Name Tag Creator ... -------- ----------- David david ... 2010 julia ... . . . . . . FilesTags Table
File ID Tag Name ------- -------- 1 2010 1 April 1 David 2 2010 2 Julia 3 Friends . . . . . .
Solution:1
SELECT FilesTags.Tag_name, COUNT(*) as number_of_occurences FROM FilesTags GROUP BY FilesTags.Tag_name;
Solution:2
Zaki's response answers one of your questions but not the other one.
"Is it worth to add a "Count" column to the Tags table to hold this info ?"
It really depends on what you are going to do with the counts. Which in turn depends on the nature of your web site.
SO makes displays the counts alongside the tags; that's partly because its target audience is geeks and geeks like numbers. But also the count is useful information when it comes to tagging a question. Plus there's the Taxonomist's badge to consider, and the display of new tags on the Moderators' page. In other words, there's a lot of calls on the count, so it makes sense to hold and maintain the count against each tag.
Other sites don't extract nearly as much juice out of their tags. But, as an example, say you want to display a tag cloud; you might want to be avoid counting all the entries in FileTags each time you render it. Holding the count would then be a help.
But remember, writes can be expensive. So have a clear idea what you are going to do with the counts before you decide to store them. Maintaining them asynchronously, perhaps in a batched job, would be a good idea.
Solution:3
To find the number of times tag "ABC" occurs just do
SELECT COUNT(*) FROM FilesTags Table WHERE Tag Name = "ABC";
Hope this helps.
Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
EmoticonEmoticon