Best Way to make a "hashtag" system

Question

This is possible a problem what i lost more time to think how to do correct, thinking in the best optimized way to do.

I have a table "Contents", and need to add the option to associate multiple "hashtags" or "tags" or "keywords" to the contents, for example, the content like "fried potatos with ketchup" have the "keys": "potato", "ketchup" and "fried".

And when i make a search by a word, for example "potato", i need to show the contents that have in it tags this word.

The question is, what its the best structure to do this, thinking in the speed of results because the content table is a MyISAM mith more than 30 Millon rows.

I think in this:

Make 2 more tables, "contents_hashtags" (id(INT11), content_id(INT11), hashtag_id(INT11)) and "hashtags" (id(INT11), hashtag(VARCHAR(40))) the 2 tables in InnoDB

When the user create/modify the table content, I search in the hashtags table and get the IDs, if a hashtag that don't exist, create it in the table hashtag, if exists, get the ID, using this IDs create the inserts in the table contents_hashtas asociating contents <-contents_hashtas-> hashtahs

In the search, make the JOINS (LEFT/RIGHT/INNER dude..) and make a search by LIKE ?? by exact(hashtag = "XXX") or FULL TEXT SEARCH?

Is this method correct/fast? I dont know how run this with a big row counts and big traffic..

Coorg

Answer 1

Actually, one extra table is enough

"hashtags" (id(INT11), hashtag(VARCHAR(40))), content_id(int11))

Now you can simply add hastag's by name. To get all hashtags for a content, use

SELECT hashtag FROM hashtable WHERE content_id=$content_id

TO add a remove a hashtag, delete it using its id or hashtag itself. To get content for specific hastags, just use

SELECT ct.* from hashtable ht, contenttable ct WHERE ht.hashtag=$hastag and ct.id=ht.content_id

and so on

Vincents

Answer 2

Three tables will do the trick:

Contents, Hashtags, and ConTags. ConTags will be a junction table containing contents.id and hashtags.id. In this way, you can attribute multiple hashtags to each item in Contents.

SELECT * FROM Contents c, Hashtags h LEFT JOIN ConTags t ON c.Id = t.ConId AND h.Id = t.HashId

Alternatively, set Hashtags Name and ContentId as unique key and the junction table is no longer needed

Marlomarlon

What the methods are to fast in search? the 3 table or 2 table method? In the 3 table method is a direct relation by JOINs, but in the 2 table method the hashtag are "duplicated" for the contents (because id+hashtag cant repeat). If i make a search for example: content.active = 1 AND (contents.name = 'xxx' OR (Contags.hashtag = 'XXX' and Content.id = Contags.content_id)) – Coorg

Upvoted for "Alternatively, set Hashtags Name and ContentId as unique key ". No need for 3 tables 2 will be enough. – Heraclitean