Questions related to Row Format, Encoding, and performance

[Hamid Sarfraz]

I want to alter an existing table to set the row_format to FIXED.

I have few questions related to this:

1. Does such change increase the amount of data transfer from mysql server to client application (may be because of increased row size) on SELECT queries to that table?

2. Is there any reason to move to FIXED format when there are all Numeric fields in the table. Updates are negligible and occur rarely. There are heavy INSERTs and heavy SELECTs. Most (or may be all) of the fields are indexed (causing lesser reads of actual data). I thought of doing this just to make my data reliable and gain some performance benefit but i am not sure whether mysql actually converts the row format when there is no VARCHAR or CHAR column in the table. I am not sure about the default row format of mysql when there is no text field. Please tell me if this is wrong question.

3. When i changed the row format of one of the test tables to fixed width, i found that text fields with utf8_* collation take 3 times (appx.) more space than ascii_* collation. UTF8 is ASCII compatible. I have set the encoding of that text field to utf8_* because i want to make my application complete utf8 compatible and i have set the connection encoding to utf8 too. Now, If i change the collation of that text field to ascii_*, what would be the behaviour of the connection encoding? Would data in that column be safe for urf8 encoded connection? If there occures any on the fly conversion of encoding, what may be the implications for performance and reliability?


Note: Increased disk usage is not a problem unless it becomes a bottleneck in the performance.


I am expecting some great answers, not links to useful resources. Any help in this regard is welcome.

Thanks.



Edited 3 time(s). Last edit at 06/21/2011 03:16AM by Hamid Sarfraz.