5

I just read the accepted answer of this question, which left me with this question.

Here's a quote from that answer:

"But since you tagged this question with MySQL, I'll mention a MySQL-specific tip: when your query implicitly generates a temporary table, for instance while sorting or GROUP BY, VARCHAR fields are converted to CHAR to gain the advantage of working with fixed-width rows. If you use a lot of VARCHAR(255) fields for data that doesn't need to be that long, this can make the temporary table very large."

As I understand it, the advantage of CHAR is that you get fixed-width rows, so doesn't a VARCHAR in the same table mess that up? Are there any advantages of using CHAR when you have a VARCHAR in the same table?

Here's an example:

Table with CHAR:

CREATE TABLE address (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    street VARCHAR(100) NOT NULL,
    postcode CHAR(8) NOT NULL,
    PRIMARY KEY (id)
);

Table without CHAR:

CREATE TABLE address (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    street VARCHAR(100) NOT NULL,
    postcode VARCHAR(8) NOT NULL,
    PRIMARY KEY (id)
);

Will the table with CHAR perform any better than the table without CHAR, and if so, in what situations?

Community
  • 1
  • 1
Erik B
  • 40,889
  • 25
  • 119
  • 135
  • Possible duplicate - http://stackoverflow.com/questions/3408930/does-anyone-have-considerable-proof-that-char-is-faster-than-varchar – ocodo Feb 08 '11 at 20:56
  • The `char` table will use less space, because it doesn't have an extra field for "length of the postcode string" in every record. – Anon. Feb 08 '11 at 20:59
  • @Slomojo: That question is about whether there is any advantages of `CHAR` at all and that's not what I'm asking. – Erik B Feb 08 '11 at 21:18
  • @Anon: I don't really care about that byte, but it's a valid point. – Erik B Feb 08 '11 at 21:20
  • Regardless, CHAR(8) is a better description of the data than VARCHAR(8). – OrangeDog Feb 08 '11 at 22:07
  • 2
    In SQL Server one advantage of `char` is for updates. If a field gets updated to a longer value the row may not fit where it was meaning a forwarding pointer needs to be used (for a heap) or a page split in a clustered index. I guess similar considerations may well apply in MySQL as well but don't know enough about the storage implementation to offer a definitive answer. – Martin Smith Feb 08 '11 at 22:15
  • I guess char followed by varchar will make some difference than the varchar followed by char as shown in your example. – shantanuo Feb 10 '11 at 09:40

1 Answers1

2

"VARCHAR" basically sets a maximum length for the field and only stores the data that is entered into it, thus saving on space. The "CHAR" type has a fixed length, so if you set "CHAR(100)", 100 character worth of space will be used regardless of what the contents are.

The only time you will gain a speed advantage is if you have no variable length fields in your record ("VARCHAR", "TEXT", etc.). You may notice that Internally all your "CHAR" fields are changed to "VARCHAR" as soon as a variable length field type is added, by MySQL.

Also "CHAR" is less efficient from a space storage point of view, but more efficient for searching and adding. It's faster because the database only has to read an offset value to get a record rather than reading parts until it finds the end of a record. And fixed length records will minimize fragmentation, since deleted record space can be reused for new records.

Hope it helps.

Knowledge Craving
  • 7,955
  • 13
  • 49
  • 92
  • Are you saying that postcode will be treated as a `VARCHAR` just because street is a `VARCHAR`? Will this just happen internally or will MySQL tell me that my `CHAR` field now is a `VARCHAR` field? – Erik B Feb 08 '11 at 21:55
  • @Erik - It will happen internally. Edited the answer. – Knowledge Craving Feb 08 '11 at 22:03
  • I don't have any actual proof that what you're saying is true, but it's upvoted, it makes sense and no one has opposed, so I'll accept it as my answer. – Erik B Feb 09 '11 at 08:44