Link to home
Create AccountLog in
Avatar of Evan Cutler
Evan CutlerFlag for United States of America

asked on

Data modeling question with MySQL

Greetings, philisophical question.

I have two FCK text boxes that lets a user write what ever they want there.
In theory I'm not supposed to allow limitations, but I don't think that's going to be an issue.

I was thinking of using a LongText data type.
i was wondering if this is a good idea.

Secondly, I was wondering if I should remove that field from it's table and snowflake it off as it's own table.  would that be better for performance?

Thanks much.
Just looking for ideas.
Avatar of tankergoblin
tankergoblin

I dont know how long is your string

The answer really depends on the volume of strings you intend to store.

If only a few KB then i think is not an issue. It should be fine to store the string in the database.  Obviously your database will be a little bit larger because you have the text, but that's ok.

When you say snowflake it off as it's own table is that mean you want to create a new table for your long string and reference to another table?
Avatar of Dave Baldwin
Probably depends on what you want to do with them.  Text and Blob types are not stored 'inline' according to this page.  Apparently 'pointers' are stored directly in the tables that tell MySQL where to find the actual data.
http://dev.mysql.com/doc/refman/5.5/en/storage-requirements.html
Avatar of Evan Cutler

ASKER

that's the thought.  I don't know...
it could be like 5-10 paragraphs of information.

I thought maybe pulling it off the table, and maintaining a child-parent relationship with that table... I don't know...that's why I'm asking.  If not, I can leave it in there.  no biggie to me.

I just want to know what's in practice.
Ok.

If  only few paragraph i don't think will more than 30KB

To maintaining a child parent relationship you need a reference id.
Storing a reference to another table which itself has a reference to an external file holding the text  would slow down the performance.

To maximum the performance try not to use SELECT *. Retrieve the record that you need only.
According to the link above, maximum row size is 65,535 bytes.  If you subtract all the other fields, you can use a VARCHAR() of the remaining bytes.  Probably on the order of 60,000 plus bytes?  With appropriate indexes, that probably is not a problem.
LONGTEXT is for fields up to 4GB.
The effective maximum length of LONGTEXT depends on the configured maximum packet size in the client/server protocol and available memory.

and the string that is less than 30KB is not consider large string..
SOLUTION
Avatar of crangry
crangry
Flag of Canada image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
ASKER CERTIFIED SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
As kinda cool the term implies...
I actually cannot take credit for it.
Snowflake is a Dimensioning term for Enterprise Data Warehousing
http://en.wikipedia.org/wiki/Snowflake_schema

However, I do like the thought of increasing my availability by having it on it's own.  As each record has it's own limit, I can up the limits by snowflaking it into it's own table which it sounds like will increase performance because I am going to query alot against the other values, and increase size, because row limitations will be less effective against it's size.

Thanks much guys.

Oh, and by the way.  Doctors are filling out biographies in these boxes.  I'm not sure 38K is going to be enough :).

Thanks again.
You should have mentioned it was Doctors in the first place...