Best practices for MYSQL table creation

Table Storage Type

It is best practice to use the innodb as storage engine while creating the table. They are also other storage engines available but if you want reliable and rollback transactions then innodb is the best engine to go with.

Table Compression

An InnoDB table created with ROW_FORMAT=COMPRESSED can use a smaller page size on disk than theconfigured innodb_page_size value. Smaller pages require less I/O to read from and write to disk, which is especially valuable for SSD devices.

Primary key should be there Creating any unique field in the table as primary key is the good practice but it will be best Specifying a primary key for every table using the most frequently queried column or columns, or an auto-increment value if there is no obvious primary key.

Row creation date It is also good practice to create a timestamp type field creation_date for audit purpose of the table so when table grows we know when that record is created.

Row updating date

It is also good to add update_date field in the table so when table grows we can audit when any row

gets updated.

Indexing the most searched fields

Always index the most searched fields in the query for getting the select queries faster.

Length of datatypes

Length of data types keep them according to the requirements don’t put every string field type as text

as if you know the length of the string will not exceed certain limit then use varchar(1) as datatype.

Similarly follow the same for other field types.

Information Schema

One more benefit of innodb is we have stats available for each innodb table in the information schema