mySql data type TEXT?

Posted on 2005-04-05
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
    LVL 95

    Expert Comment

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

    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

    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)

    Author Comment

    Thanks mish33,

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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    Suggested Solutions

    Title # Comments Views Activity
    sum28 challenge 31 82
    zeroMAx challenge 20 66
    scoresAverage challenge 8 58
    wordappend challenge 8 44
    This article is filled with multiple code samples and explanations for mathematical calculations. They are as follows: 1. General tips 2. Quadratic formula 3. Object collision 4. Projectile path General Tips       Here are some of my tips f…
    RIA (Rich Internet Application) tools are interactive internet applications which have many of the characteristics of desktop applications. The RIA tools typically deliver output either by the way of a site-specific browser or via browser plug-in. T…
    Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …
    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…

    759 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

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now