MySQL Database basics – part 1

Database design is very important part of web development. Properly designed database simplifies business logic, increases both query speed and security of online system. Mistakes made on level of table design can be responsible also for poor user experience. One of common mistakes is setting wrong field data types. If all you need to hold in a field is first or second customer’s name, or street and city name, usually CHAR data type (fixed-string length from 1 to 255 characters) or VARCHAR data type (variable-length string from 1 to 255 characters) is perfectly enough. If saving data space is important, VARCHAR type would be more useful, because text strings shorter than 255 characters placed in CHAR field are right-padded with spaces up to full 255 characters. However, MySQL SELECT statement works quicker with CHAR types, because all records have fixed length. So, as you see, VARCHAR vs CHAR actually means making choice between importance of database storage vs database performance. Usually performance is more important (valuable).

Setting all fields of a table as TEXT is one of more serious mistakes if data for storage are numbers or short strings/texts. TEXT data types are good only for texts longer than 255 characters, according to below specification. So, keeping customer’s first or second name in a field with TEXT data type is mistake. TEXT data type can store 65,535 bytes of data, definitely too many for our purpose. Data types listed below are used for text storage (for instance pages of text):

  1. TINYTEXT :: 256 bytes
  2. TEXT :: 65,535 bytes
  3. MEDIUMTEXT :: 16,777,215 bytes
  4. LONGTEXT :: 4,294,967,295 bytes

The difference between VARCHAR or CHAR types is that it is impossible to assing default value to TINYTEXT type, and for creating temporary tables MySQL uses disk space instead of memory as it is in case of VARCHAR or CHAR types.