MySQL data types?

[Nancy OShea]

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

[osgood_]

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]

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.

[osgood_]

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 

[sinious]

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]

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.

[sinious]

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!

[Nancy OShea]

I understand what you're saying about trade-offs.  You never get something for nothing & I don't want to introduce anything that will make the site slower.   I had been using TEXT from the beginning.  But the people who enter data into the product CMS [myself included] were not paying strict attention to how long those descriptions were getting.  In some cases they were the same length as the long_descriptions which is silly.  I've since added a maxlength to that data field.  I will run some speed tests and see how things go.  It's always something...

Nancy O.

[sinious]

Well there's one for the client side folks! Any text input can easily have the character length validated before that became your problem.

I made a twitter account just to respond to one post one day (recently actually) and the 140 character limit made me batty haha. I don't even want to say how deep I went into the JavaScript obfuscation trying to look for a way to sneak around that limit. All in good fun but, well, they're very good at validation. Even if I got through on the client side, I'd get truncated in the back, as your users are. But from the beginning Twitter was more than happy to slap me around visually with character length warnings.

[Nancy OShea]

No excuses here.  I had whooping cough & 2 cracked ribs when I coded this site 2 years ago. While coding was a good distraction for what ailed me, I freely admit making mistakes.  Not critical ones.  Just a few dumb ones.   Incidentally, whooping cough is awful!!  Don't ever get it.

Nancy O.

[sinious]

Oh you did the client side too? We've all been in perfect health and neglected worse. Glad you got better and leave it to you to have all of that going on and still champion through work! Dare I say most would be on TDI, burning through a Netflix series. Keep on keepin on! (-Joe D.)