Skip to main content
Nancy OShea
Community Expert
Nancy OSheaCommunity Expert
Community Expert
September 8, 2015
Question

MySQL data types?

  • September 8, 2015
  • 2 replies
  • 1218 views

I have a product database.

It contains both long_descriptions (text) and short_descriptions.

I want to limit length of short_descriptions to 255 characters.

Which is preferred -- varchar(255) or tinytext?

Thanks,

Nancy

This topic has been closed for replies.

2 replies

sinious
sinious
Legend
September 8, 2015

A few things we'll need to know to really answer this.

Before that, here's a caution. tinytext holds 255 (1 byte) values. Many character sets support multi-byte due to languages such as Chinese which have huge character sets requiring multibyte values. For reference:

(MySQL Ref)

Since UTF8 can support 1, 2 and 3 byte characters, you could find yourself reducing your tinytext (which only supports 2 byte) by half. So be sure you understand the content going in the field because varchar does not suffer from this. varchar(255) is 255 characters with either 1 or 2 bytes overhead used for length, 1 byte if it is 255 bytes or less or 2 bytes of the characters byte count exceeds 255 (regardless how many characters are in it).

That said, the typical reason you'd want to offer a short description can be multifold. You may want to display that on mobile versus the long description. It might be for search engine optimization on finding products. It could be for too many reasons that we can't answer.

Knowing these things would help because it affects the solution:

- What MySQL engine are you using (MyISAM, InnoDB, etc?)

- What is are the purposes and uses of this short description?

- How much traffic do you realistically expect this portion of your database to get? Now this doesn't mean that you get 20,000 visitors a day, it means digging a little deeper into what they actually do on the site. If you're solely building this short description because you find those 20k people per day perform 10+ searches, that ramps up the server usage to 200,000 hits minimum. The more information the merry here.

- Is this running on your own dedicated server or a site host? This matters because under the hood, types like VARCHAR can be entirely searched via index whereas any TEXT type can be a BLOB which requires temporary tables, adding a significant impact in performance on certain types of queries.

Once we know what the purpose and traffic is, especially any problems you've encountered or are trying to solve, that'll give a better answer.

    Nancy OShea
    Community Expert
    Nancy OSheaCommunity ExpertAuthor
    Community Expert
    September 8, 2015

    Thanks for the detailed reply, sinious.  I wasn't aware of the 1 byte limit so I guess that's a reason to avoid tinytext.

    • Dedicated Linux server.
    • MySQL engine MyISAM
    • Traffic for this domain -- approx 4,500 sessions and 10,000 page views per month.
    • UTF-8, no Chinese or Japanese characters.
    • short_descriptions are displayed on product summary and search results pages only.
    • long_descriptions are displayed on product detail pages.

    I think I'll take Osgood's advice and use varchar(255).

    Thanks to everyone who replied.  I learned something new today!

    Nancy O.

    Nancy O'Shea— Product User & Community Expert
    sinious
    sinious
    Legend
    September 8, 2015

    Just keep in mind while TEXT types do create extra temp tables because they are internally stored BLOBs that are separate from your main table, they perform that from storage. So while they would be a bit slower in running expressions such as 'LIKE' comparisons, their read speed is very memory friendly! In contrast, varchar has a hefty memory and index footprint.

    Let me put it this way. If people are performing far more reads (page views) than searches, a TEXT or CHAR type uses much less memory (it's storage based, not memory and fully indexed) and should perform better.

    If people do a lot more searching than browsing (using LIKE or REGEX comparisons), varchar will be more performant in your usage due to utilizing (typically full) indexes and using more RAM.

    I actually think using a larger TEXT type is better for your short descriptions if you have a lot of products. Nothing is worse than running out of RAM and dropping a request during checkout while someone searches for products containing the letter 'a' hehe.

    There's always tools like MySQLslap to performance test these situations.

    Good luck!

    O
    osgood_
    Legend
    September 8, 2015

    Nancy O. wrote:

    I have a product database.

    It contains both long_descriptions (text) and short_descriptions.

    I want to limit length of short_descriptions to 255 characters.

    Which is preferred -- varchar(255) or tinytext?

    Thanks,

    Nancy

    Personally I use 'varchar' for pretty much everything expect long descriptions in which case I use 'text'

      Nancy OShea
      Community Expert
      Nancy OSheaCommunity ExpertAuthor
      Community Expert
      September 8, 2015

      I mostly use varchar too. I just wondered if there is any advantage/disadvantage in using tinytext which allows a max of 255 characters as well.

      Nancy O.

      Nancy O'Shea— Product User & Community Expert
      O
      osgood_
      Legend
      September 8, 2015

      Nancy O. wrote:

      I mostly use varchar too. I just wondered if there is any advantage/disadvantage in using tinytext which allows a max of 255 characters as well.

      Nancy O.

      I'm not that technical - I'll leave that to sinious. I'm more 'trial and error'. If the water starts running after I turn on the tap, job done. If I understand what I've done, that's a bonus 

      Terms & ConditionsAccessibility statement
      Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices