One of the main problems that we must confront when designing an application as possible to optimize queries to the database for this load as quickly as possible.
When optimizing our database, we must pay close attention not only to the consultations, but also the scheme we are creating, because if we start from a poorly constructed scheme, it is more likely that we get our instructions are executed inefficiently.
When optimizing our database, we must pay close attention not only to the consultations, but also the scheme we are creating, because if we start from a poorly constructed scheme, it is more likely that we get our instructions are executed inefficiently.
Here are some tips to keep in mind.
1. Be careful when designing the database schema
The size of a database is defined by the number of entries you have stored by the size of a row, so you need to look to the size of an input as low as possible. This is where the data type of each column comes into play.
It is advisable to look for the type of data that best suits the information is stored. It makes no sense to use a TEXT type for storing a simple postcode, since in this case we will always have 5 digits, and with a type of char (5) data could save a large number of bytes.
It is advisable to look for the type of data that best suits the information is stored. It makes no sense to use a TEXT type for storing a simple postcode, since in this case we will always have 5 digits, and with a type of char (5) data could save a large number of bytes.
2. Partition Tables
In many cases, design tables where much information is stored but at the moment of truth only use some data assiduously. In these cases, it is best to divide the table into two: in the information we used, while in the other the rest of the information will be stored.
This partition will be getting less frequent table takes up less memory.
This partition will be getting less frequent table takes up less memory.
3. Proper use of indexes
Many developers slow queries to the database to solve it by adding indexes to it. This may work in some cases but overuse rates can also bring problems.
Recall that the indexes are used to indicate the engine of the database column that will be used frequently, so we get that information is stored in memory. Each of these indices occupies a space proportional to the number of rows in the table memory, so if you have defined many indexes, the size will be very large occupying.
Recall that the indexes are used to indicate the engine of the database column that will be used frequently, so we get that information is stored in memory. Each of these indices occupies a space proportional to the number of rows in the table memory, so if you have defined many indexes, the size will be very large occupying.
4. Forget the query SELECT *
Whenever we use this query, we are bringing all the fields that are part of the table, fields that in many cases will not use. One thing, the more information we bring us, the slower the consultation must be taken into account, so it is advisable to limit queries to the columns we need.
5. Forget the nested SELECT and use JOIN
Using the JOIN usually give better results than using nested SELECT within a query. Remember that as a general rule, you can convert any nested in a JOIN SELECT easily.
I hope you've found these tips useful. Don’t forget to follow me on Twitter, hang out on Facebook and Google, play on Pinterest or check out my LinkedIn profile.
