MySQL Text Fields and Memory Usage

I am working on an application that allows users to dynamically add questions to web forms. We use MySQL as the backend, and I am trying to find the fastest, most efficient way of storing the form data.

Previously, we stored the data in a separate table for each form section. The columns were named according to a system that allo开发者_StackOverflow社区wed us to map the dynamic question to its storage location. The drawbacks were that the storage mapping system was badly designed, which made modifying forms with existing data a nightmare. Also, MySQL limitations on the memory per row limited the number of questions we could have per section.

Consequently, I am looking at using a single table to contain all form data. Because essay questions are allowed, I am considering using Text or MediumText as the field type for the actual data. But, I am concerned about RAM usage when running queries. When I run a query for the data, will MySQL be smart enough to allocate only the memory needed for the data in the field (even if it's a small integer) or will it allocate the full amount allowed for the MediumText field?

Furthermore, is there a better way you can think of with regards to storing the data for a dynamic database like this?

Amy

Yes, as you create your table objects for storing these large text fields, try using compression in your tables. It sounds like a perfect fit, if you have the InnoDB plugin enabled.

http://dev.mysql.com/doc/innodb-plugin/1.0/en/innodb-compression-tuning-when-data.html http://dev.mysql.com/doc/innodb-plugin/1.0/en/innodb-compression-usage.html

A simple table definition for your answers might be something like:

CREATE TABLE test_answers (
answer_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
test_id INT UNSIGNED,
question_id INT UNSIGNED,
answer_body TEXT,
PRIMARY KEY(answer_id, question_id)
) ENGINE=InnoDB
 ROW_FORMAT=COMPRESSED 
 KEY_BLOCK_SIZE=4;

Might get away with moderately-sized text fields but generally nicer to store large fields in a separate table.