mySql data type TEXT?

I am writing a web-based application in PHP using mySql database.  One of the things I need to store is a text type memo field.   I won't need it on every record so I'm trying to come up with the best solution.  The way I see it I have 3 options:

1. make a separate linked table for those records that need the memo using and id column and a TEXT type column.

2. make a separate linked table for those records that need the memo but store the address of a text file on the server.

3. make a separate linked table for those records that need the memo and store the memos in a flat text file on the server.

I am trying to find the best balance between access speed and space usage.  Any input would be appreciated.

Who is Participating?
mish33Connect With a Mentor Commented:
Option 1 makes sense if you have no other variable width fields like TEXT, VARCHAR or BLOB.
In that case you will get static (or fixed) table storage format. If you have them than table will be dynamic and you will not get speed boost.

In dynamic format (default for tables with TEXT columns)  "each record uses only as much space as is required. However, if a record becomes larger, it is split into as many pieces as are required, resulting in record fragmentation. For example, if you update a row with information that extends the row length, the row becomes fragmented. In this case, you may have to run OPTIMIZE TABLE or myisamchk -r from time to time to get better performance. Use myisamchk -ei to obtain table statistics." (see for more info)
Lee W, MVPTechnology and Business Process AdvisorCommented:
I use option 1 most of the time.  More efficient in my opinion to keep all your data in the database where you can access and manipulate it as needed.  Storing it elsewhere doesn't make it any easier to retrieve.
ibanjaAuthor Commented:
Thanks leew,

Do you know? Am I correct in my understanding that a record takes up a fixed amount of space in that TEXT field regardless of how much text is actually in it?
ibanjaAuthor Commented:
Thanks mish33,

That answers my question nicely and clears it up for me.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.