Consider splitting off variable-length fields

There is a speed advantage to using fixed-length fields because MySQL can search through them quicker than through variable-length fields. However, if you have just one variable-length field in a table, the entire table is considered variable-length. As such, it is important to look at your tables to see whether they could easily become fixed-length and trying it out.

Consider messageboards, for example - they contain fields such as the ID number of the poster, the ID number of the message, the title of the message, the date it was posted, and, of course, the actual body of the message. Of those, the two ID numbers and the date are all integers (a Unix timestamp would do fine for the date), and the message title can be a CHAR(100) - long enough for most messages, and then some. However, the text of the message should really be a TEXT data type because messages should be able to have any length - this would make the entire table variable-length, hence slowing things down.

Now consider that your average messageboard visitor, unless you get people posting particularly important messages, is likely to only read a few of the messages posted. This means that the majority of their database accesses will be caused by the generation of the messageboard index, where current topics are listed. Given that when a visitor goes to a messageboard they might see a list of 100 messages posted, and from that list they would only read perhaps 10, it is clear to see that it is a major waste to have message text inside each message.

So, the ideal solution is to split off each message into two tables, for example "mbmsgs" and "mbmsgstext" - one to hold the key information about a message (who posted it, what its title is, etc), and the other to hold the message text. With this solution, the table that is read the most (mbmsgs) would be fixed-length, making the generation of the messageboard index faster.

Copyright ©2015 Paul Hudson. Follow me: @twostraws.