StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POBest way to store "tags" for speed in enormous table
text
Body
copied!<p>I'm developing a big content site, with a table "contents", with more than 50 Million of records. Here's the table structure:</p> <pre><code>contain id(INT11 INDEX), name(varchar150 FULLTEXT), description (text FULLTEXT), date(INT11 INDEX) </code></pre> <p>I wan to add a "tags" to this contents.</p> <p>I'm think 2 methods:</p> <ol> <li><p>Make a varchar(255 FULLTEXT) "tags" column in table contents. Store all tags separated by comas, and search row by row (Which I think this will be slow) using MATCH & AGAINS.</p></li> <li><p>Make 2 tables. First table name "tags" with columns id, tag(varchar(30 INDEX or FULLTEXT?)), "contents_tags" with id, tag_id (int11 INDEX) and content_id (int11 INDEX) and search contents by a JOINS of 3 tables (contents - contents_tags - tags) to retrieve all contents with the tag(s). </p></li> </ol> <blockquote> <p>I think this is slow and memory killer because a ENORMOUS JOIN of 50M table * contents_tags * tags.</p> </blockquote> <p>What is the best method to store tags to make it as efficient as possible? What is the fastest way to search by a text (for example "movie 3d 2011" and simple tag "video") and to locate contents.?</p> <p>The size of the table (approx. 5Gb now without tags). The table is a MYISAM because I need to store name and description of the table contents in FULLTEXT to string search (users ca search now by this fields), and need the best speed to search by tags.</p> <p>Any with experience in this?</p> <p>Thanks!</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload