• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
  • Skip to footer

02 7903 0216

     

Open Ticket

    

Client Area

FASTDOT Web Hosting Australia

FASTDOT Web Hosting Australia

Australian Cloud Hosting

  • Web Design
    • eCommerce Development
    • Branding
    • Web Design
  • Domains
    • Register
    • Domain FAQ
    • Domain Extensions
    • MyDNS Manager
    • Domain Transfer
  • Hosting
    • WordPress Hosting
    • Sydney cPanel Hosting
    • Magento Hosting – eCommerce Website
    • eCommerce Options
      • Managed Magento Hosting
      • PrestaShop Hosting
      • OpenCart Hosting
      • CS-Cart Hosting
    • Business Emails
  • Cloud Servers
    • VMware ESXi Hypervisor
    • Equinix – ISO Certified Data Centres
    • Cloud Hosting Info
    • Virtual Machines
  • Resources
    • Partner Program
    • Transferring Websites
    • WordPress Tips
    • Email Tutorials
    • Hosting in Australia
    • Apps Galore
    • Tutorials & News
      • SEO
      • VMware VPS Cloud
      • PHP and MySQL
      • Security
      • Linux Guides
      • Server Management
Order Now

VARCHAR vs. TEXT for MySQL Databases

October 8, 2021 by Editor

When you’re building a database for a web application, one of the most impactful decisions is the data type you choose for text data fields. MySQL provides multiple string data types, each with unique characteristics and trade-offs. The data type you choose affects how much data you can store, where it’s stored, the functionality available to you, and the performance of everyday database operations.

In this article, we’ll look at one frequently asked question concerning MySQL string data types: should you use a VARCHAR column or a TEXT column to store textual website data?

MySQL VARCHAR vs. TEXT: The Short Answer

If you’re looking for a TL;DR, it’s this: use VARCHAR if your data is of variable length and you know it fits into VARCHAR’s 65,535 character limit. In most circumstances, VARCHAR provides better performance, it’s more flexible, and can be fully indexed. If you need to store longer strings, use MEDIUMTEXT or LONGTEXT, but be aware that very large amounts of data can be stored in columns of these types.

MySQL VARCHAR vs. TEXT: The Longer Answer

To explain why VARCHAR is the right default choice, we’ll need to look at MySQL’s string data formats. For our purposes, these fall into two main groups, CHAR and VARCHAR types and the TEXT types, which are based on MySQL’s binary object BLOB types. There are other string types—SET and ENUM—but they’re not relevant to this article.

We’ll be talking about characters and the amount of space they consume. However, we’re not going to discuss the precise definition of a character or the effect of using different character sets. Take a look at the MySQL documentation to learn more about character lengths and multibyte character sets.

First, CHAR and VARCHAR:

  • CHAR is a fixed-length column with a maximum length of 255 characters. You declare the size when you create the table. If you store fewer characters than the fixed length, MySQL pads the remainder with spaces. Therefore, CHAR always consumes the same amount of storage.
  • VARCHAR columns store variable-length strings up to 65,535 characters. You specify the maximum length that you intend to store when you create the table. VARCHAR columns are not padded, which means they consume as much space as the length of the string (plus one or two bytes for a prefix that indicates the string’s length).

CHARs are great for storing short strings when you know how long they are. You can also use VARCHAR to store short strings—VARCHAR(40), for example—but it can store any string up to the maximum column size, using a variable amount of storage space.

Next, TEXT and its variants. Text is based on the BLOB (binary large object) type. These are variable-length data types, and they come in three main variants:

  • TEXT has a maximum length of 65,535 bytes—the same as VARCHAR.
  • MEDIUMTEXT has a maximum length of about 16 megabytes.
  • LONGTEXT has a maximum length of about 4 gigabytes.

In many respects, TEXT is similar to VARCHAR: it stores variable-length strings up to 65,535 bytes. For both, the amount of space consumed depends on the length of the string. However, there are some key differences:

  • TEXT columns can only be indexed for a specified length. MySQL doesn’t index the full length of TEXT data types (except for full-text search indexes).
  • The full length of the string can’t be used for sorting.
  • Queries involving TEXT columns can lead to the creation of a temporary table on disk instead of in memory. This is because MySQL’s memory storage engine does not support TEXT data types. There is a significant performance penalty to using disk-based tables, which means queries using TEXT can be much slower.
  • TEXT data may be stored off the table, with a pointer to the string stored on the table. Accessing data stored in this way is slower. VARCHAR data is always stored on the table. If data is frequently retrieved, inline storage offers faster performance.

Taken together, these qualities of TEXT mean that, for many circumstances involving a web application, it’s preferable to use VARTEXT when storing variable-length text of fewer than 65,535 characters.

None of this means that you must use VARCHAR. TEXT is a reasonable choice if the limitations we’ve described are unimportant to you. However, there are circumstances where TEXT has undesirable consequences that don’t arise if you use VARCHAR.

If you want to store longer variable-length strings, MEDIUMTEXT and LONGTEXT are your best option. However, you may run into network and other system limitations if you try to insert large amounts of data. Throwing multi-gigabyte BLOBs into your database is rarely wise, so be sure to check the data’s size before storing it.

VARCHAR in Action

To see a real-life example of how these MySQL data types are used, let’s explore a couple of tables in WordPress’s database with cPanel’s built-in phpMyAdmin tools. You can examine the data types of any MySQL database on your server by opening phpMySQL, locating the database in the left-hand column, and clicking the Structure link adjacent to the relevant table.

If you look at the comments table, you’ll see that WordPress’s developers have used VARCHAR for shorter text fields: comment type, comment agent, comment author, URL, and so on. But they have chosen to use a TEXT field for the comment content, a column unlikely to be used in an index.

In the post table, a similar pattern is evident. VARCHAR for post status, post password, and post name. The post content column, which is likely to exceed VARCHAR’s space limit, is stored in a LONGTEXT column with a theoretical maximum of 4 GB.

PhpMySQL is just one of the many web hosting, database management, and automation tools built into cPanel & WHM. You can read more about managing database with cPanel in:

As always, if you have any feedback or comments, please let us know. We are here to help in the best ways we can. You’ll find us on Discord, the cPanel forums, and Reddit. Be sure to also follow us on Facebook, Instagram, and Twitter.

More great articles

Using cPanel Webmail for Branded Email Accounts
The cPanel Mail Server – cPanel Eats Its Own Dog Food
cPanel Application Manager and App Deployment 101
Changing up the Feedback System

Category iconTutorials Tag iconProducts

Primary Sidebar

Recent Posts

  • How to Upload Files Using the cPanel File Manager?
  • How To Increase the PHP Max Upload Size in cPanel®?
  • Choosing a hosting platform in 2021
  • VARCHAR vs. TEXT for MySQL Databases
  • What Are the Best Shopify® Alternatives in 2021?
Building cPanel Web Hosting on Amazon AWS
Linux Security Enhancement with cPanel & WHM®
Upcoming MySQL Changes
How To Use PuTTY SSH With cPanel®

Categories

  • Application Hosting
  • Australian Cloud Hosting
  • cPanel Hosting
  • DNS and Domains
  • eCommerce Hosting
  • Email Tutorials
  • Legals
  • Linux Tutorials
  • Operating Systems
  • PHP and MySQL
  • Register a Domain Name
  • Search Engine Optimization
  • Security
  • Server Management
  • Tutorials
  • VMware Cloud Hosting
  • Web Hosting Tutorials
  • WordPress Tips & Tricks
Australian web hosting

Footer

Recent Posts

  • How to Upload Files Using the cPanel File Manager?
  • How To Increase the PHP Max Upload Size in cPanel®?
  • Choosing a hosting platform in 2021
  • VARCHAR vs. TEXT for MySQL Databases
  • What Are the Best Shopify® Alternatives in 2021?

Legals

  • Terms&Conditions
  • SLA
  • Acceptable Usage Policies
  • Privacy Policies
  • About FASTDOT
  • ISO Certified

Contact

Australia: +61 02 7903 0216
Submit a Support Ticket

Company

FASTDOT.COM.AU PTY/LTD
ACN 002 454 631
200 Bourke Rd, Alexandria NSW 2015 Australia
Processing by eWay, PayPal and Bitcoin

blankNSW Government Provider

Made with in Sydney/Australia
  • Facebook
  • Twitter

Copyright © 2022 FASTDOT.COM

All rights reserved. Return to top