Let's assume I have a large number of member records that have to be accessed constantly. Let's also assume each member record has to include a bio, which can be anywhere from 0 to quite a large number of characters, will be accessed much less often than the other fields of the record, and will be updated every few days to a week. Am I better off keeping the bio in the main table, where it will likely cause all sorts of gaps and reduce efficiency for the majority of views, or put the bio in a separate table, where I'll have to access two tables instead of one the minority of views where someone wants to view the bio?

Or in other words, how much will having a TEXT field in a table reduce efficiency, assuming I OPTIMIZE TABLE reasonably often? Does the reduction in efficiency only have to do with gaps caused by TEXT contents getting larger and having to move, or is there a reduction in efficiency inherent to TEXT fields with large contents? Are accesses significantly faster if the table is more compact?

Also, to a lesser extent, what's the tradeoff for having indexes? What's the ratio of views to updates that makes an index break even in terms of efficiency? Assume 10,000 records or so.

Unconsidered delete by holli - enough keep votes (5/1/3)

In reply to OT: MySQL - TEXT field efficiency? Do I include in table or separate? by TedPride

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.