0

I am dealing with a decently large table in a MySQL database, about 10 million rows. One of the columns is VARCHAR(50), but the rest are all fixed size (int, datetime, etc). Would changing this field to be CHAR(50) improve MySQL's ability to scan the table, given that each row would be a fixed size?

I understand the performance benefits of CHAR vs VARCHAR--that is not my concern. I had simply read that fixed size rows can speed up MySQL when dealing with large tables, and just want to know if this is true and to what degree.

Improve this question

1 Answer 1

Reset to default
3

Short Answer: No

Long Answer:

Fixed row size is an old wives' tale. It had a rare use case when MyISAM was the only Engine. With it dying and InnoDB dominating, it is really out of date.

If your Varchar is usually short, then your table takes about twice as much disk space as a CHAR(50) than as VARCHAR(50). That means twice the I/O. I/O is the "slow" part of the performance, not the ability to do an UPDATE "in place" as MyISAM might do and InnoDB does not do (or at least not exactly).

These days, 10M rows is only medium-sized. But even for a billion-row table, I would give the same advice -- even more emphatically.

InnoDB works with 16KB "blocks". Sure, if the text in your Varchar expands causing a block split the costs a little effort. But meanwhile, the block is holding twice as many rows. Etc.

Another point: Locating the row is a far bigger cost than splitting it into columns.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

It's also worth mentioning that even in the edge case of MyISAM tables, one would have to make all the columns fixed-width. It would have no benefit if there are any variable-width columns left in the given table.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.