DevChunks

Tighten up the schema

It does not matter how good 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 are planning to have everybody on the planet registry 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 popular because people believe that 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. Possibly the best approach might be to normalize the schema first and then denormalize certain fields. This will make the schema much more logical and reduce the need to optimize your databases prematurely.

Share:

One Response

Leave a Reply

Your email address will not be published.

More Posts

How to use PHP Type Declarations

PHP has developed into a more strictly typed language in recent years. Type Declarations help with that and are a fairly new addition to PHP,

Iteration

If you have come to JavaScript from a background in Java or C, then you will like the variation on the for loop. It is

Context Object Manipulation

One of the key concepts of JavaScript is the keyword this, which signifies the JavaScript context object. If you write an inner function, you can access

Polymorphic Functions

It is possible to define multiple functions with the same name in JavaScript, and the last one of those that you define will be the