When we design database schema it’s recommended to follow the beast practices to use memory in optimal way and to gain performance. Following are 10 MySQL best practices
Always try to avoid redundancy
We can say database schema designed is the best one if it is having no redundancy. If you want to avoid redundancy in your schema, normalize it after you design.
Database normalization is the process of organizing columns and tables in relational database to avoid redundancy. Find more about normalization here
Use (unique) indexes on foreign key columns
We use foreign keys for data integrity and to represent relation. Some times these are result of process called normalization. When tables are mutually related obviously we can’t query the data without using joins
Avoid using varchar for fixed width column instead use char
Choose the right one CHAR vs VARCHAR. CHAR(15) will just allocate the space for 15 characters but VARCHAR(15) will allocate the space only required by number of characters you store.
Always use explain to investigate your queries and learn about how mysql is using indexes
EXPLAIN statement is very handy in mysql. I’m sure it will spin your head. This statement will give you analyzed report. Where you can use it to improve your queries and schema. It works on both select and update. If you try it on update queries it will that query as select and will give you the report.
Use right data type
Choosing right data type for you column will help you to get rid of many bottle necks. MySQL query optimizer will choose the indexes based on data type you used in query and column datatype. There are many MySQL datatype.
Use ENUM if required
ENUM is one datatype that mysql supports. By using this you can save lot of memory if you have predefined and predictable values in your database column.
Don’t use too many indexes, it will slow down the inserts and updates. Only use the indexes on selected column
As you know indexes will help you query data much faster than expected. It’s very tempting to you indexes on unintended columns. Choosing index on every column or unnecessary columns will get you slow inserts and updates. You need to think of indexes as seperate table. Where MySQL needs to create a index for every insert in seperate table/file. It’s extra overhead.
Tune mysql default parameters
MySQL comes with default parameters. These parameters are not suitable if you want use mysql on dedicated machine or production. You have to tune these parameters. Formally we call them as system variables.
Always create an account with associated hosts instead of wildcard %
MySQL manages the user with associated hosts. i.e, the user root@localhost can’t login to mysql from everywhere except localhost. but root@% can login from every where. Using only associated hosts will mitigate many attacks those are in your blind spot.