Search

Best practices for MYSQL table creation

Updated: Mar 7, 2019





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

Recent Posts

See All

Cassandra data models

Objectives Data modeling is the hardest part for the people coming from RDBMS back ground. The goal here is to define the basic rules one should keep in mind before designing the schema. If you follow

Call

T: +44 7825018241

Follow me

 

© 2020 by GoplarDB 

All Rights Reserved

Powered by: Goplar LTD

  • LinkedIn Social Icon
  • Twitter Clean