Tighten up the schema

by jeremyjones on March 14, 2011

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.

Leave a Comment

{ 1 trackback }

Previous post:

Next post: