Chenimal - mysql-guideline

gistfile1.txt

### Fundamental
- Use auto_increment integer as primary key for **all** tables(make its name be `id`)
- Comment is required for new columns& new tables
- Use `utf8` as the default charset for character type
- Single table size < 50 millions records
- Do not store images, files as binary type in database
- Do not connect **production** database through `ldev`, `dev`, `test` and `sandbox` environment
- Do not do **stress test** on production database

### Naming convention
- all db name, table name and column name, **must** be 
  - combination of lower case letter and underscore(**NO** capital letters)
  - no longer than 32 characters
  - **do not** use mysql preserved words (especially column name). (e.g.: `block`, `key(s)`, `type(s)`, `name(s)`, `value(s)`)
- for temperary tables and databases, must start with `tmp_` as prefix, and end with `_2017XXXX` as postfix
- for backup tables and databases, must start with `bak_` as prefix, and end with `_2017XXXX` as postfix

### Table design
- always use `not null` unless really need difference between empty and null.
- always explicitly indicate column's default value
- integer
  - make all integer columns be `UNSIGNED` unless it might have negative values
  - use smallest integer type(tinyint, smallint, mediumint, int, bigint) that cover the range we need.
  - use default display width for integer (e.g. `int` rather than `int(10)`)
- datetime
  - use `int` to store time

### Index
- all index columns **must** be **not null**
- no more than 5 indexes for one single table
- no more than 5 columns for one single index(multi-column index)
- each table **must** have primary key, and it must be `integer`
- **do not** use UUID, MD5 or HASH as primary key
- **do not** add index on column with low cardinality(e.g. gender, only three possible values)
- **do not** add unique index on primary key
- naming: 
  - non-unique index:`idx_column1_column2`
  - unique index: `uniq_column1_column2`
  - all must be lower case

### SQL
- explain your query
- **do not** use force index
- use unique key as much as possible
- make query as simple as possible
  - split big query into small queries
  - do not use `store procedure`
  - do not use `case when then else end`. Please, do this logic in application level
  - do not do calcuation on index column
- use `union all` instead of `union` if possible
- when using `like`, do not use `%` as prefix (e.g. "%somthing")
- do not do reverse lookup like `not in`, `not like`
- avoid using sub-query