Re: Questions related to Row Format, Encoding, and performance
Posted by: Rick James
Date: June 24, 2011 11:05PM

Executive summary: Don't bother with FIXED.

Details ("Great answers"):

> Does such change increase the amount of data transfer from mysql server to client application.
Maybe -- Putting 'abcd' into VARCHAR(50) needs to send 4 bytes (plus quotes, etc); putting it into CHAR(50) needs to send all the trailing spaces, too. More importantly, fetching the data from disk takes longer, and is less cacheable.

> ... utf8 take 3 times ...
Yes. CHAR(50) takes 150 bytes on disk. ("Collation" is irrelevant; "character set" is the key.) 'abcd' in VARCHAR takes only 4 bytes, even for utf8. (Plus 1-2 disk bytes for length.)

> ... change field to ascii, what would be the behaviour of the connection encoding?
Watch out for SET NAMES. It may have to/from convert to utf8 during the transfer. Ascii characters are identical in either character set. So, if you are limiting your text to ascii, ascii vs utf8 does not matter much for VARCHAR, but matters 3x for CHAR on disk.

> Increased disk usage is not a problem unless it becomes a bottleneck in the performance.
Is all your data small enough to fit in memory? Are the cache settings set correctly? Are you using InnoDB or MyISAM? Don't forget that the data will be written to disk eventually (for persistence).

I am now going to give you "links to resources" -- where I have answered the same question on these same forums:
http://forums.mysql.com/read.php?21,423433 (how table rows are stored at the file level)
http://forums.mysql.com/read.php?20,414883 (Text(1024))
http://forums.mysql.com/read.php?10,400581 (MYSQL manual using MANY frequently changing Varchar(255))
Consider them to be extensions of these "great answers".

Options: ReplyQuote


Subject
Written By
Posted
Re: Questions related to Row Format, Encoding, and performance
June 24, 2011 11:05PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.