“Improve your efficiency with database indexes”

Different factors, different responsibilities
In order to keep an optimal efficiency level, hosting services need to invest in a decent technical infrastructure. Combell attaches a great deal of importance to quality and has been investing in high quality hardware for years and the required expertise to use it. But this is not enough to guarantee high performance: the developer also carries a great responsibility.

It is not just about using efficient programming codes, but also about minimizing the processing time of external data sources. The most common implementation is the database. Via SQL, you can filter data from your database to use in your application. Whatever the application speed, you will always depend on your database.





Database indexes and the metaphor of the book
For small data sets, the dependency in terms of efficiency is minimal. It is only when your database contains a lot of data that you will notice a structural slowdown. One important solution is the use of indexes. This principle can be perfectly compared to the list of keywords contained in a book.

If you were requested to count the amount of times the word “house” appears in a 10-page book, you would have to start counting from page one. That would be feasible, but with a 1000-page book, your search would be time-consuming. But if you would have a list of keywords in your book, you should be able to count the amount of times the term appears in a wink and be able to know where it appears in particular.

Indexing as part of the database design process
Indexing your database is not something you do by accident – it is an essential part of the database design process. When creating database tables, it is important to know which fields to index. The functional conditions must also be taken into account: the fields to be searched should be known in advance.

The reflex to index each field must be suppressed, because indexing is a “trade off”: on the one hand, you would increase the efficiency of the search, but on the other hand, keeping the indexes up-to-date requires time and resources. The balance between reading and writing must be constantly monitored. That is exactly why it is a good idea to know – thanks to your functional analysis – which are the most important fields and how they must be filtered. From a non functional point of view, the necessary knowledge about the read/write ratio is an added bonus.

Implementation
The implementation of the index can be done quite easily through an SQL statement. You could use the “create index” syntax, but you could also define indexes within an “alter table” statement. For text fields it’s important to indicate the index length.

The more compact the index, the better the results. If the index results are too compact however, the efficiency decreases. If e.g. you want to index the term “Domain name” and choose a 3-character index, only “Dom” can be indexed. Terms such as “Domino”, “Domain” or even just “Dom” would also be found. An index which is too long, takes too much space and requires time to be updated.

In short: a good balance between the index fields and the index length will allow you to get a maximum efficiency.