The best way to make a hashtag system

Question

Perhaps the problem is that I lost more time to think about how to do it right, thinking in the best way possible.

I have a table “Contents” and you need to add an option to associate several “hashtags” or “tags” or “keywords” with content, for example, the contents of “chips with ketchup” have “keys”: “potatoes”, “ ketchup "and" fried. "

And when I do a search on a word, for example, "potato", I need to show the contents containing the tags for that word.

The question is what is its best structure for this, thinking in speed of results, because the content table is MyISAM mith over 30 rows of Millon.

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))) 2 tables in InnoDB

When the user creates / changes the contents of the table, I search the hashtags table and get identifiers, if a hashtag that does not exist, create it in the hashtag table, if exists, get the identifier, use these identifiers to insert in the table content_hashtas asociating content <-contents_hashtas -> hashtahs

In the search, do JOINS (LEFT / RIGHT / INNER dude ..) and search for LIKE ?? Exact (hashtag = "XXX") or FULL TEXT SEARCH?

Is this method right / fast? I don't know how to run this with lots of lines and lots of traffic.

Answer 1

Actually, one additional table is enough

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

Now you can just add hastag by name. To get all the hashtags for the content, use

 SELECT hashtag FROM hashtable WHERE content_id=$content_id 

To add hashtag removal, delete it using your id or hashtag. 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 

etc.

Answer 2

Three tables will do the trick:

Content, Hashtags and ConTags. ConTags will be a join table containing contents.id and hashtags.id. This way you can attribute multiple hashtags for each element in the content.

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

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