Varchar vs Text
I have did a lot of research on making decision about using Varchar or Text. Here I list out all the importance points when choosing Varchar or Text :
- Varchar size can grow when records are updated frequently. Reference at here.
- Comparison can be done if using Varchar, eg :
select your_column from your_table
where your_column like '%dogs%'
- Comparison cannot be done if using TEXT unless the storage engine is MYISAM.
- Performance / speed of Varchar is faster than Text. Reference at here.
- Text column should be separated out to other table. When a table has TEXT or BLOB columns, the table can't be stored in memory. This means every query (which doesn't hit cache) has to access the file system - which is orders of magnitude slower than the memory.
Therefore you should store this TEXT column in a seperate table which is only accessed when you actually need it. This way the original table can be stored in memory and will be much faster.
Think of it as separating the data into one "memory table" and one "file table". The reason for doing this is to avoid accessing of the filesystem except when neccessary (i.e. only when you need the text).
You don't earn anything by storing the text in multiple tables. You still have to access the file system.
Note : some other people said better don't move out TEXT column to other table if u need to do many queries per second. Reference at here.
- 1 byte per character in latin1 encoding, but up to 3 in UTF8.
- Every table has a maximum row size of 65,535 bytes. utf8 characters require 3 or 4 bytes per character, so for a CHAR(255) CHARACTER SET utf8 column, the server must allocate 255 × 3 = 765 bytes per value. A table cannot contain more than 65,535 / 765 = 85 such columns. A VARCHAR(255) CHARACTER SET utf8 column takes 2 bytes to store the length of the value, so each value can take up to 767 bytes. NULL columns on MyISAM database require additional space in the row to record whether their values are NULL. Each NULL column takes one bit extra. Storage size of NULL and NOT NULL columns is the same size on InnoDB database. Reference at : here.
- utf8 characters require 3 or 4 bytes, So 3000-character varchar column can take up to 9000 bytes(can support 7 columns only) or 12000 bytes(can support 5 columns only).
- INDEXes are limited to 768 or 1000 bytes. INDEXes cannot have TEXT, but can have VARCHAR.
- max size of row is different between InnoDB and MyISAM tables.
- InnoDB stores at least 768 bytes of each BLOB/TEXT/VARCHAR column locally into the row itself. This means that you can in practice have only at most 10 long columns in the row.
- If max size of row is not enough to have too many columns on the table, u can split the columns into multiple tables. Reference at : here.
Reference at : here and here.
conclusion : If you are using mysql version 5.0 or above, and if you can ensure max size of row (65,535 bytes) is enough for all columns, then you can use varchar. For example, utf8 characters require 4 bytes, So 3000-character varchar column which need 12000 bytes per column. So the table can support 5 of such columns only.
Comparison between Char vs Varchar at here.
- Varchar size can grow when records are updated frequently. Reference at here.
- Comparison can be done if using Varchar, eg :
select your_column from your_table
where your_column like '%dogs%'
- Comparison cannot be done if using TEXT unless the storage engine is MYISAM.
- Performance / speed of Varchar is faster than Text. Reference at here.
- Text column should be separated out to other table. When a table has TEXT or BLOB columns, the table can't be stored in memory. This means every query (which doesn't hit cache) has to access the file system - which is orders of magnitude slower than the memory.
Therefore you should store this TEXT column in a seperate table which is only accessed when you actually need it. This way the original table can be stored in memory and will be much faster.
Think of it as separating the data into one "memory table" and one "file table". The reason for doing this is to avoid accessing of the filesystem except when neccessary (i.e. only when you need the text).
You don't earn anything by storing the text in multiple tables. You still have to access the file system.
Note : some other people said better don't move out TEXT column to other table if u need to do many queries per second. Reference at here.
- 1 byte per character in latin1 encoding, but up to 3 in UTF8.
- Every table has a maximum row size of 65,535 bytes. utf8 characters require 3 or 4 bytes per character, so for a CHAR(255) CHARACTER SET utf8 column, the server must allocate 255 × 3 = 765 bytes per value. A table cannot contain more than 65,535 / 765 = 85 such columns. A VARCHAR(255) CHARACTER SET utf8 column takes 2 bytes to store the length of the value, so each value can take up to 767 bytes. NULL columns on MyISAM database require additional space in the row to record whether their values are NULL. Each NULL column takes one bit extra. Storage size of NULL and NOT NULL columns is the same size on InnoDB database. Reference at : here.
- utf8 characters require 3 or 4 bytes, So 3000-character varchar column can take up to 9000 bytes(can support 7 columns only) or 12000 bytes(can support 5 columns only).
- INDEXes are limited to 768 or 1000 bytes. INDEXes cannot have TEXT, but can have VARCHAR.
- max size of row is different between InnoDB and MyISAM tables.
- InnoDB stores at least 768 bytes of each BLOB/TEXT/VARCHAR column locally into the row itself. This means that you can in practice have only at most 10 long columns in the row.
- If max size of row is not enough to have too many columns on the table, u can split the columns into multiple tables. Reference at : here.
| CHAR( ) | A fixed section from 0 to 255 characters long. |
| VARCHAR( ) | A variable section from 0 to 255 characters long. (depend on mysql version. 5.1 version varchar support up to 65500 characters long, reference at here.) |
| TINYTEXT | A string with a maximum length of 255 characters. |
| TEXT | A string with a maximum length of 65535 characters. (TEXT occupies a number actual length of your data + 2 bytes. Text store external file and store pointer value to database.). |
| BLOB | A string with a maximum length of 65535 characters. |
| MEDIUMTEXT | A string with a maximum length of 16777215 characters. |
| MEDIUMBLOB | A string with a maximum length of 16777215 characters. |
| LONGTEXT | A string with a maximum length of 4294967295 characters. |
| LONGBLOB | A string with a maximum length of 4294967295 characters. |
| TINYINT( ) | -128 to 127 normal 0 to 255 UNSIGNED. |
| SMALLINT( ) | -32768 to 32767 normal 0 to 65535 UNSIGNED. |
| MEDIUMINT( ) | -8388608 to 8388607 normal 0 to 16777215 UNSIGNED. |
| INT( ) | -2147483648 to 2147483647 normal 0 to 4294967295 UNSIGNED. |
| BIGINT( ) | -9223372036854775808 to 9223372036854775807 normal 0 to 18446744073709551615 UNSIGNED. |
| FLOAT | A small number with a floating decimal point. |
| DOUBLE( , ) | A large number with a floating decimal point. |
| DECIMAL( , ) | A DOUBLE stored as a string , allowing for a fixed decimal point. |
| DATE | -YYYY-MM-DD. |
| DATETIME | -YYYY-MM-DD HH:MM:SS. |
| TIMESTAMP | -YYYYMMDDHHMMSS. |
| TIME | -HH:MM:SS. |
conclusion : If you are using mysql version 5.0 or above, and if you can ensure max size of row (65,535 bytes) is enough for all columns, then you can use varchar. For example, utf8 characters require 4 bytes, So 3000-character varchar column which need 12000 bytes per column. So the table can support 5 of such columns only.
Comparison between Char vs Varchar at here.
How to use phpMyAdmin Database
To create a Foreign Key :
1) Convert all MySQL tables from MyISAM to InnoDB.
2) Set Index onto column which u want it to be foreign key.
3) Click Relation view > Choose parent column (primary key for other table) > Choose CASCADE on "ON DELETE" drop down list.
If you can't apply Foreign Key to a particular field, because of error #1216 or #1452 - Cannot add or update a child row: a foreign key constraint fails. Why? Because parent's table doesn't has the records that child's table has, so can't apply foreign key on the child's table. Fortunately, I've found some handy SQL queries using LEFT OUTER JOIN to clean child tables and easily delete unmatched records. As always, don't forget to BACK EVERYTHING UP before attempting these queries!! First, to find "wayward" records with no matching id in a corresponding parent table:
SELECT * FROM `agenda` LEFT OUTER JOIN meetings on
agenda.meeting_id=meetings.meeting_id WHERE
meetings.meeting_id is NULL;
DELETE agenda.* FROM `agenda` LEFT
OUTER JOIN `meetings` ON agenda.meeting_id =
meetings.meeting_id WHERE meetings.meeting_id IS NULL;
Reference at : http://www.mytechmusings.com/2008/04/using-foreign-keys-in-mysql.html and http://lists.mysql.com/mysql/212302
To add a Composite Primary Key :
You can use SQL ALTER TABLE myTable
ADD CONSTRAINT pk_myConstraint PRIMARY KEY (Column1,Column2)
Or use the interface, after the table is created, you can add an index (type 2 or 3 in textbox of columns). Then select columns to be the composite primary key, then choose "Primary Key" from Index Type drop down list.
Reference at : http://www.sitepoint.com/forums/mysql-182/composite-key-through-phpmyadmin-290795.html and http://stackoverflow.com/questions/1545571/how-do-i-make-a-composite-key-with-sql-server-management-studio
To reset auto increment id on table :
You don't need to waste time to create a new table, you can just delete all records in existing table then run SQL : ALTER TABLE `table_name` AUTO_INCREMENT =0
Reference at : http://www.knowledgesutra.com/discuss/ttlist-reset-auto-increment-phpmyadmin.om
To find where is the foreign keys of particular primary key (getting error when trying to rename the field of primary key because there is foreign key on other table linked by the primary key) :
There are 2 different solutions :
1) SQL to list out all tables that has foreign keys of a particular primary key :
select ku.*
from INFORMATION_SCHEMA.table_constraints tc
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE ku ON
(tc.CONSTRAINT_NAME = ku.CONSTRAINT_NAME and tc.CONSTRAINT_SCHEMA = ku.CONSTRAINT_SCHEMA )
where constraint_type='FOREIGN KEY' and ku.REFERENCED_TABLE_NAME = 'your_table_name'
2) You go rename the field to get error, then type SQL :
SHOW ENGINE INNODB STATUS
Then look at the "LATEST FOREIGN KEY ERROR" section. Reference at http://stackoverflow.com/questions/7463221/sql-lists-out-all-foreign-keys-of-a-primary-key
SERIAL is the best datatype for auto increament id field, because SERIAL is BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE. It can accept integers from 0 up to 18446744073709551615 (max is 20 digits only), if I assign length 50, it will still accept 20 digits only, it won't accept 50 digits. 18446744073709551615 is the largest integer accepted. You cannot use 2 Serial on two fields in a table because 1 table can has 1 AUTO_INCREMENT field only. The word UNSIGNED is attribute. Reference at here.
I cannot find the different between BIGINT Signed and Unsigned, however the different between CHAR Signed and Unsigned :
-Signed char, which gives you at least the -128 to 127 range.
-Unsigned char, which gives you at least the 0 to 255 range.
-Signed number uses one bit to determine whether the number is positive or negative, an unsigned number is positive.
Reference at here.
Different between datatype Char and Varchar :
Char :
- Use Char if all of records values have the same length. Eg, if u set Char to 5 characters, each records will be 5 bytes.
- Don't use Char if all of records values have difference length. Eg, u store "abc123" on Char(10), it will be "0000abc123" or "abc1230000", u need extra work to delete all 0000. This feature could lead to many irritating bugs during development and makes testing harder). Info at here.
- Char can be faster and more space efficient.
- Use Char if you need to update the records frequently, because it won't grow the size everytime update the records.
Varchar :
- Use Varchar if all of records values have different length. Eg, if you save record "love1234", the size will be 8 bytes (to save the value) + 1 byte (to save the length of value). Varchar need extra 1 or 2 bytes to save the length of value. Info of datatype at here. The example of size of data / records are at here.
- Don't use Varchar if records need to be updated frequently because their size will grow, which can cause extra work. if a row grows and no longer fits in its original location, the behavior is storage engine-dependent. Info at here.
Example :
store the word "FooBar" :
CHAR(6) = 6 bytes (no overhead)
CHAR(10) = 10 bytes (4 bytes of overhead)
VARCHAR(10) = 7 bytes (1 byte of overhead)
Conclusion : Use Char only if you have fixed length of records values. Don't use Char if the length of records values are different, even 1 character different also must NOT use Char.
Comparison between Varchar vs Text at here.
Normalization :
If some of the fields are missing (null) for a bunch of the rows, they make a good candidate for splitting off -- the extra table would have fewer rows. You would use LEFT JOIN to put things back together when needed. Eg :
FROM t1 JOIN t2 on t1.ID = t2.ID
JOIN / INNER JOIN show the records on both tables. LEFT JOIN / LEFT OUTER JOIN show the records on 1st table. RIGHT JOIN / RIGHT OUTER JOIN show the records on 2nd table.
1) Convert all MySQL tables from MyISAM to InnoDB.
2) Set Index onto column which u want it to be foreign key.
3) Click Relation view > Choose parent column (primary key for other table) > Choose CASCADE on "ON DELETE" drop down list.
If you can't apply Foreign Key to a particular field, because of error #1216 or #1452 - Cannot add or update a child row: a foreign key constraint fails. Why? Because parent's table doesn't has the records that child's table has, so can't apply foreign key on the child's table. Fortunately, I've found some handy SQL queries using LEFT OUTER JOIN to clean child tables and easily delete unmatched records. As always, don't forget to BACK EVERYTHING UP before attempting these queries!! First, to find "wayward" records with no matching id in a corresponding parent table:
SELECT * FROM `agenda` LEFT OUTER JOIN meetings on
agenda.meeting_id=meetings.meeting_id WHERE
meetings.meeting_id is NULL;
DELETE agenda.* FROM `agenda` LEFT
OUTER JOIN `meetings` ON agenda.meeting_id =
meetings.meeting_id WHERE meetings.meeting_id IS NULL;
Reference at : http://www.mytechmusings.com/2008/04/using-foreign-keys-in-mysql.html and http://lists.mysql.com/mysql/212302
To add a Composite Primary Key :
You can use SQL ALTER TABLE myTable
ADD CONSTRAINT pk_myConstraint PRIMARY KEY (Column1,Column2)
Or use the interface, after the table is created, you can add an index (type 2 or 3 in textbox of columns). Then select columns to be the composite primary key, then choose "Primary Key" from Index Type drop down list.
Reference at : http://www.sitepoint.com/forums/mysql-182/composite-key-through-phpmyadmin-290795.html and http://stackoverflow.com/questions/1545571/how-do-i-make-a-composite-key-with-sql-server-management-studio
To reset auto increment id on table :
You don't need to waste time to create a new table, you can just delete all records in existing table then run SQL : ALTER TABLE `table_name` AUTO_INCREMENT =0
Reference at : http://www.knowledgesutra.com/discuss/ttlist-reset-auto-increment-phpmyadmin.om
To find where is the foreign keys of particular primary key (getting error when trying to rename the field of primary key because there is foreign key on other table linked by the primary key) :
There are 2 different solutions :
1) SQL to list out all tables that has foreign keys of a particular primary key :
select ku.*
from INFORMATION_SCHEMA.table_constraints tc
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE ku ON
(tc.CONSTRAINT_NAME = ku.CONSTRAINT_NAME and tc.CONSTRAINT_SCHEMA = ku.CONSTRAINT_SCHEMA )
where constraint_type='FOREIGN KEY' and ku.REFERENCED_TABLE_NAME = 'your_table_name'
2) You go rename the field to get error, then type SQL :
SHOW ENGINE INNODB STATUS
Then look at the "LATEST FOREIGN KEY ERROR" section. Reference at http://stackoverflow.com/questions/7463221/sql-lists-out-all-foreign-keys-of-a-primary-key
SERIAL is the best datatype for auto increament id field, because SERIAL is BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE. It can accept integers from 0 up to 18446744073709551615 (max is 20 digits only), if I assign length 50, it will still accept 20 digits only, it won't accept 50 digits. 18446744073709551615 is the largest integer accepted. You cannot use 2 Serial on two fields in a table because 1 table can has 1 AUTO_INCREMENT field only. The word UNSIGNED is attribute. Reference at here.
I cannot find the different between BIGINT Signed and Unsigned, however the different between CHAR Signed and Unsigned :
-Signed char, which gives you at least the -128 to 127 range.
-Unsigned char, which gives you at least the 0 to 255 range.
-Signed number uses one bit to determine whether the number is positive or negative, an unsigned number is positive.
Reference at here.
Different between datatype Char and Varchar :
Char :
- Use Char if all of records values have the same length. Eg, if u set Char to 5 characters, each records will be 5 bytes.
- Don't use Char if all of records values have difference length. Eg, u store "abc123" on Char(10), it will be "0000abc123" or "abc1230000", u need extra work to delete all 0000. This feature could lead to many irritating bugs during development and makes testing harder). Info at here.
- Char can be faster and more space efficient.
- Use Char if you need to update the records frequently, because it won't grow the size everytime update the records.
Varchar :
- Use Varchar if all of records values have different length. Eg, if you save record "love1234", the size will be 8 bytes (to save the value) + 1 byte (to save the length of value). Varchar need extra 1 or 2 bytes to save the length of value. Info of datatype at here. The example of size of data / records are at here.
- Don't use Varchar if records need to be updated frequently because their size will grow, which can cause extra work. if a row grows and no longer fits in its original location, the behavior is storage engine-dependent. Info at here.
Example :
store the word "FooBar" :
CHAR(6) = 6 bytes (no overhead)
CHAR(10) = 10 bytes (4 bytes of overhead)
VARCHAR(10) = 7 bytes (1 byte of overhead)
Conclusion : Use Char only if you have fixed length of records values. Don't use Char if the length of records values are different, even 1 character different also must NOT use Char.
Comparison between Varchar vs Text at here.
Normalization :
If some of the fields are missing (null) for a bunch of the rows, they make a good candidate for splitting off -- the extra table would have fewer rows. You would use LEFT JOIN to put things back together when needed. Eg :
FROM t1 JOIN t2 on t1.ID = t2.ID
JOIN / INNER JOIN show the records on both tables. LEFT JOIN / LEFT OUTER JOIN show the records on 1st table. RIGHT JOIN / RIGHT OUTER JOIN show the records on 2nd table.
