Page 1 of 1
type of tables for relationship between them
Posted: Wed Jul 27, 2005 2:54 am
by php12342005
questions:
1. I read an article on the internet which says:
type of tables must be created as innoDB for generating relationship between them, right?
what does innoDB and other types of tables mean?
2) types of field
what are differences between text, char(..), varChar(..)?
thanks
Posted: Wed Jul 27, 2005 5:41 am
by anjanesh
Abt 2) -
String Types
Difference is mainly
Storage Requirements
CHAR(M) M bytes, 0 <= M <= 255
VARCHAR(M) L+1 bytes, where L <= M and 0 <= M <= 255
BLOB, TEXT L+2 bytes, where L < 2^16
Posted: Wed Jul 27, 2005 7:47 am
by nielsene
If you want to use foreign keys with referential integrity, yes you must use InnoDB tables for both ends of the relationship.
Posted: Wed Jul 27, 2005 8:41 am
by php12342005
hi,
thanks for reply.
1. ---------------------------------
further question about field type:
anjanesh wrote:
CHAR(M) M bytes, 0 <= M <= 255
VARCHAR(M) L+1 bytes, where L <= M and 0 <= M <= 255
BLOB, TEXT L+2 bytes, where L < 2^16
if so, TEXT and VARCHAR are better than CHAR from size of storage's view.
For example, if user inputs "Hello boy" in a field.
bytes used varies with type of the field according to the input
(type, bytes used)
CHAR(50), 50
VARCHAR(50), 9+1=10
TEXT, 9+2=11
is my understanding above correct?
if yes, why people use CHAR rather than VARCHAR very often?
2. ---------------------------------
further question about table type:
question:
does InnoDB use more storages than default table type? - I guees so. why default type of a table is NOT InnoDB, what is its drawback?
Posted: Wed Jul 27, 2005 8:48 am
by timvw
php12342005 wrote:
if so, TEXT and VARCHAR are better than CHAR from size of storage's view.
For example, if user inputs "Hello boy" in a field.
bytes used varies with type of the field according to the input
Because with VARCHAR your dbms has to calculate the real length each time. Where with CHAR it has a fixed length and doesn't need to calculate the exact record length when navigating through the records...
So as usual, it's a choice between calculations <-> memory
php12342005 wrote:
why default type of a table is NOT InnoDB, what is its drawback?
In my experience people that use mysql don't care about data integrity. That would explain why they choose myisam and don't care about referential integrity etc..
Posted: Wed Jul 27, 2005 10:29 am
by nielsene
InnoDB takes more storage space, and I beleive and (at least used to, it might be better now) uses rather coarse grain locking which reduces preformence to gain partial ACID. (Most other ACID compliant databases uses a finer locking model which results in less blocking)