MySQL Text Fields and Memory Usage

Question

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

Previously, we saved data in a separate table for each section of the form. The columns were named according to a system that allowed us to map a dynamic issue to its storage location. The disadvantages were that the storage mapping system was poorly designed, which made modifying forms with existing data a nightmare. In addition, MySQL's memory limitations per row limited the number of questions we could have in each section.

Therefore, I am considering using a single table containing all the form data. Since the essay questions are resolved, I am considering using Text or MediumText as the type of field for the actual data. But I'm worried about using RAM when doing queries. When I run a query for 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 entire allowable value for the MediumText field?

Also, is there a better way that you can store data for a dynamic database as follows?

Amy

Answer 1

Yes, when you create your table objects to store these large text fields, try using compression in your tables. This sounds perfect 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 look something like this:

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; 

Answer 2

It may go away with moderate-sized text fields, but in general it is better to store large fields in a separate table.