Search code examples
mysqldatabasedatabase-performance

MySQL Performance - CHAR(64) vs VARCHAR(64)


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?


Solution

  • 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!