MySQL Performance - CHAR(64) vs VARCHAR(64) [closed]

Question

In an InnoDB database a field to store domain names that will be indexed and 64 chars long, can be either of datatype CHAR(64) or VARCHAR(64), where the first would pad the data so all records are the same length. Apparently this would provide some performance gain as far as speed, but would also take up more disk space due to padding.

In theory, and without conducting any bench testing, would it be true that CHAR(36) does provide some performance gain?

Torch

bench test it!. take you about as long as it did to write your question! – Wrath

Answer 1

With the most recent versions of MySQL running on 21st century servers, most people doing this kind of work suggest *Use the data types best suited to your problem." Make your code and data easy to read and easy to understand!

Why? because MySQL's indexing is good enough that the difference in performance is tiny. It's only when tables get large ( meaning on the order of 10**9 rows or more ) that the performance difference between VARCHAR() and CHAR() starts to be meaningful.

It's smarter to spend your precious development time making your app nice enough that you have a chance of attracting gazillions of users. And when you have them, you probably can get a really competent database administrator to help you fix your minor inefficiencies.

Topnotch servers cost thousands. Developers capable of really tight optimization cost tens of thousands. Being late to market costs millions. Keep it simple, get it done!

Contraption

Answer 2

Q: would it be true that CHAR(36) does provide some performance gain?

No, not with InnoDB. (The question earlier discusses values of 64 characters.)

There are a lot of factors that will have much more impact on performance than the difference between CHAR(64) vs VARCHAR(64) column.

In theory (without any bench testing), the CHAR column will cost us not just extra "disk space", but will require more I/O (fewer rows per block), more memory for InnoDB block cache, more data written to the InnoDB log (binary logging), more time for backup, and so on. (It's not just "disk space", it's time required to read and write from the disk, including backups, etc.)

This isn't to say that we couldn't setup a test that would show a slight performance benefit to the CHAR column. But that test would likely not emulate any real world workload.

If the values we are storing are always 64 characters, or close to 64 characters, then the CHAR would be the way to go. But if the values we are storing are frequently much shorter than 64 characters, the we'd opt for VARCHAR.

Caressive