mySql data type TEXT?

Posted on 2005-04-05
Medium Priority
Last Modified: 2010-04-17
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.

Question by:ibanja
  • 2
LVL 97

Expert Comment

by:Lee W, MVP
ID: 13710420
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.

Author Comment

ID: 13710614
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?
LVL 15

Accepted Solution

mish33 earned 500 total points
ID: 13712854
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 http://dev.mysql.com/doc/mysql/en/myisam-table-formats.html for more info)

Author Comment

ID: 13713594
Thanks mish33,

That answers my question nicely and clears it up for me.

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this post we will learn how to connect and configure Android Device (Smartphone etc.) with Android Studio. After that we will run a simple Hello World Program.
This article will show how Aten was able to supply easy management and control for Artear's video walls and wide range display configurations of their newsroom.
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question