MySQL: Storage of multiple text fields for a record

Question

An inexperienced question:

I need to store about 10 unknown-length text fields per record into a MySQL table. I expect no more than 50K rows in total for this table but speed is important. The database actions will be solely SELECTs for all practical purposes (and searches will be done using an integer PK id only). I'm using InnoDB.

In other words:

id | text1 | text2 | text3 | .... | text10

As I understand that MySQL will store the text elsewhere and use its own indicators on the table itself, I'm wondering whether there's any fundamental performance implications that I should be worrying about given the way the data is stored? (i.e. several "sub-fetches" from the table).

Thank you.

Answer 1

Are you going to be selecting records by the id (an indexed key) or by the data in the text fields?

If the former, performance shouldn't be a concern. If the latter, make sure you index whatever field(s) you will be using for your loads.

Answer 2

Essentially what you're asking is if those 10 TEXT fields will require 10 separate disk seeks for each row you retrieve. I'm not sure if InnoDB will write these all in a row and be able to quickly read them, and so, with most performance questions, your best bet is to create and run some simple tests and find out what the implications are (unless someone else comes along and claims to know!)

A couple of things to note:

• Since your table isn't that large and you're mostly using SELECTs, you could just cache the data. InnoDB can store actual data in its buffer pool (unlike MyISAM) - so try setting this value (innodb_buffer_pool_size) to be large enough to accomodate your data.
• If you are always reading all 10 columns, and wanted to use a single TEXT field, you could add some intermediary code that handles splitting the data into its 10 parts by prefixing each field with the number of characters/bytes it has, so that when you read the data back you know exactly how much space each part takes up.
• If you follow the above approach, you may need to use a larger TEXT field, like MEDIUMTEXT or LONGTEXT.