Take the 2-minute tour ×

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 allowed 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?


share|improve this question
add comment

2 Answers

up vote 1 down vote accepted

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 (
question_id INT UNSIGNED,
answer_body TEXT,
PRIMARY KEY(answer_id, question_id)
share|improve this answer
I was under the impression that MyISAM was much faster than InnoDB. And does InnoDB offer full text search capabilities? If the speeds are comparable, I would much rather use InnoDB because it has support for foreign keys as well. –  Amy Anuszewski Sep 30 '10 at 21:17
There are gives and takes with the differences with the table engines. If you can design the schema properly, you can get great performance out of InnoDB. Of course, it depends on your scale and how I/O bound you are. You'll have to setup a separate keyword index if you use InnoDB as it does not have fulltext indexing. –  randy melder Sep 30 '10 at 23:08
add comment

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

share|improve this answer
add comment

Your Answer


