Tighten up your Schema in MySQL

It does not matter how well you can write queries in MySQL; if the underlying schema is not optimized, it will slow your database performance down.

This is not just removing unwanted fields, but you need to make sure that your fields are storing data with the correct size.

One example of this is a user_id column. You will only need to define it as a BIGINT if you plan to have everybody on the planet register 2.8 trillion times. The same goes for fixed-length text fields. Do not use a VARCHAR to store these values, as it will add a superfluous byte to every row.

In some quarters, database normalisation is not widespread because people believe it makes the schema too complicated without good reason. If done properly, however, normalization will reduce the amount of redundant data, and that, in turn, will reduce the size of your database tables.

In some cases, this normalization can affect performance, so some fields may need to be de-normalized to improve performance.

The best approach might be to normalize the schema and then denormalize certain fields.

This will make the schema much more logical and reduce the need to optimize your databases prematurely.

Similar Posts

One Comment

Leave a Reply

Your email address will not be published. Required fields are marked *