The Power of Database Indexing

WHAT IS DATABASE INDEXING?

Database indexing is a powerful technique used to optimise databases, resulting in significant speed improvements in the database as well as the web app or website that makes use of such databases.

Usability plays a huge role when it comes to the success of a website or web application. A key factor that contributes to usability is the speed with which the user can perform the tasks they set out to do and how quickly information can be retrieved and displayed to the user.

HERE’S HOW IT WORKS

Have you ever noticed a website that was blazing fast when launched but as months went by it just became slower and slower? There could be many factors that contribute to this deterioration in speed, however, often it’s just the database that wasn’t indexed correctly or at all.

Indexing is a way for the database to not search each and every row of data when doing a query (a method to request data) by having the stored data rearranged by certain columns behind the scenes. Doing this will have the database take up more storage space but in this day and age storage isn’t really a problem anymore, with highly decreased costs.

Best practices indicate that one would normally index a column (a column representing a field of data and a row representing a data record) that you join two or more tables on (for example an ID field) and then secondly the columns that are most searched on.

A simple example:

In the above example, we indexed the “age” column in the “persons” table. The image on the left is what you see when you open the table. The image on the right is the indexed version that you won’t see but it’s what is used internally by the database when someone does a query that involves the age column. It’s not really an exact copy of the data. There are all sorts of clever techniques used to minimize the amount of storage data the index takes up.

Let’s say we want to know which person is 25 years old. In an unindexed table, the database engine will look through every row. It will find it at id 7 after looking through id 1-6 but also continue searching for more 25 year olds. On the indexed table, the database engine will find the person at id 7 after only looking at two rows and will stop searching beyond that because the next person’s age is 28 and the database engine is aware that the column is sorted numerically.

Modern websites and web applications, often use complex queries to return data from multiple relational database tables. Therefore websites could become very slow, very quickly if it isn’t indexed.

The difference in speed can range from pages that could that several minutes to load due to not having a database index, versus the same database being indexed and returning the data in a matter of seconds, even fractions of a second in certain circumstances.

It is clear that there is a huge benefit to database indexing however it can be implemented incorrectly which can lead to other complications, such as slow speed and even expose the database to a deadlock. Therefore experience and a thorough approach is needed when performing the optimisation.

website-speed-statistics

There are many indexing methods besides the above example. Each database should be approached and indexed according to its structure, relationships of the tables, the types of data in the database and the reporting requirements (meaning what data needs to be aggregated for reporting purposes).

Applying the indexes correctly will allow you to see a significant speed improvement, not only now but also going forward.

So make sure your website’s database is indexed!

Not sure what a database index is or have severe speed issues on your website or web application? Don’t stress – we’re here to help! Reach out to us for some help!

Don't forget to share this post!