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..

Answer 1

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

Answer 2

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