0

FIXED MySQL table has well-known performance advantages over DYNAMIC table.

There is a table tags with only one description TEXT field. An idea is to split this field into 4-8 CHAR(255) fields. For INSERT/UPDATE queries just divide description into chunks (PHP function str_split()). That will make table FIXED.

Have anybody practiced this technique? Is it worth it?

topright gamedev
  • 2,617
  • 7
  • 35
  • 53
  • 1
    I think this is true for MyISAM only. See a similar question [here](http://stackoverflow.com/a/147337/2091410) and also read the references cited in the accepted answer. – Ed Gibbs Jul 17 '13 at 20:46
  • 2
    Those "well known performance advantages" with MyISAM can evaporate, when those fixed length rows occupy a lot more memory than variable length records. If fewer rows fit in memory, that may cause additional i/o, and it can actually degrade performance. – spencer7593 Jul 17 '13 at 21:01
  • I'm pretty sure it's **not** worth messing up your data model for a performance gain that you aren't even sure it exists. And even if there *was* a performance gain (which I really doubt) that must be extremely high in order to justify such an horrible model. –  Jul 17 '13 at 21:27
  • It is not worth it. Just don't use MyISAM. It's slower, broken, and antiquated. It is receiving no improvements, and it's likely to be deprecated within the next couple of MySQL versions. – Bill Karwin Jun 06 '14 at 22:48

1 Answers1

0

OK, this is done, but where it is done I have only seen it done for historical reasons, such as a particular client-server model that requires it, or for legacy reports where the segments are de facto fields in the layout.

The examples I have seen where for free form text entries (remarks, notes, contact log) in insurance/collections applications and the like where formatting on a printed report was important or there was a need to avoid any confusion in post post processing to dress the format where multiple platforms are involved. (\r\n vs \n and EBCDIC vertical tabs).

So not generally for space/performance/recovery purposes.

If the row is "mostly" this field, a alternative would be to create a row for each segment and add a low-range sequence number to the key.

In this way you would have only 1 row for short values and up to 8 for long. Consider your likely statistics.

Caveats :

Always be acutely aware of MySQL indexes dropping trailing spaces. Concatenating these should take this into account if used in an index.

This is not a recommendation, but "tags" sounds like a candidate for a single varchar field for full text indexing. If the data is so important that forensic recovery is a requirement, then normalising the model to store the tags in a separate table may be another way to go.

mckenzm
  • 1,545
  • 1
  • 12
  • 19