What is row size in a display of table structure?

[HuntsvilleMan]

I'm confused about what row length and row size mean in the mySQL table structure display.

Below is a table structure I created that shows a row length of 40 and row size of 2088 B.

When I create a second table with the identical structure it shows a row length of 40 and a row size of 222 B.

Why do seemingly identical tables have diferent row sizes - one table showing 222 B and one table showing 2088 B?

My server is provided through Yahoo web hosting and is "Server version: 4.1.14"

While the tables seem to work fine and do what I expect, I'm wondering what's going on.

 

Thanks - Mike

 

Field                Type        Collation                Null  Default  Extra

=============================================

is_confirmed      Tinytext      latin1_swedish_ci      No               

person_id          int(11)                                          No    0               

confirm_time    Text            latin1_swedish_ci        No               

message_type  Tinytext      latin1_swedish_ci      No       

uniqe_id            int(11)                                          No              Auto increment

 

In my table structure, the fields is_confirmed and message_type are single character fields.  Is there a better type than "Tinytext" for for single character fields?  It seems that when I enter a maximum field length of "1" it ignores that length specification.  When I redisplay the structure it never show the maximum text field length. 

[gizmola]

Single character field should be char(1).  Always use char or varchar if you can, rather than the text types.

 

What is confirm_time?  Mysql has date/datetime and timestamp types.

 

Don't worry about the sizes, they are simply there to indicate when you are approaching limits to mysql internals for things like the maximum data that can be stored in a row or the size of the all the combined column names, but in neither case are those internal limits a concern for you.

[HuntsvilleMan]

Thanks for the tips.  Looks like I should have used CHAR(1) rather than TEXT(1). The time value is from a PHP script where I save the value of Now().  Later I use XML to send selected rows back to a Microsoft Access PC on my desk.  Keeping data in "text" fields is the only way I have figured out to send data back to Microsoft Access.  It would be nice if there was something like an ODBC interface between my web hosting account mySQL database and my desktop Microsoft Access database.  Anything like that you know of?

 

Thanks

Mike

[gizmola]

Yes MySQL has an odbc driver.  You might have to work with your hosting company to get mysql configured to accept connections directly from your workstation, but it is doable.  http://dev.mysql.com/downloads/connector/odbc/

[HuntsvilleMan]

Do you have a feel for how reliable the ODBC interface is when implemented over an Internet connection.  It would be helpful to know what other users have experienced when using the configuration below with writes to a mySQL database.

 

        Microsoft Access on a desktop PC/ODBC <------  Internet ------> mySQL on a remotely hosted server

 

The internet does drop out from time to time.  I'm wondering if it's possible to write an ODBC dependent application based on an internet connection that will only be interrupted and recover safely but only very very rarely be corrupted by drop outs.

 

Any experience with this?

 

Mike