Indexes are a database structure to efficiently optimize your queries. A pointer is created to quickly find data instead of scanning your entire collection/table.

However, you need to know when to use or which type of index to optimize your queries.

Just as this can speed up your queries, it can slow down your entire database and increase the maintenance cost. Poorly chosen indexes decrease your performance by having to restructure all your B-tree

When to use an Index?

Analyzing your application is essential when choosing an index, which queries are being hit mostly? Does this one need to be faster? Will you give up inserting/updating speed to save time on readings?

Selecting Indexes that will be used in many queries will help your index be more accurate and performant.

Indexes should be used where you need to do a lot of reading operations on larger collections/tables or in searches that need to be fast.

When to not use an Index?

When your database has a lot of writes and read operations in indexed fields might be a lot expensive since your database will have to rewrite all your indexes.

For each writing operation, you are writing 2 times in your database, once the data and another for the index, taking up disk space and consuming more hardware to maintain the same, in large amounts slowing down your database.

Modeling your database by thinking about the number of writes operations that it will have, whether INSERT, DELETE, or UPDATE will save you a lot of headaches if you need fast READ operations.

Analyzing when you need fast insertions also will help you optimize your perfomance.

Conclusion

Choosing an index is not as easy as it seems, knowing how to analyze your indexes is an essential step to doing something performant, but when well chosen this will certainly optimize your queries.

Feedbacks on Twitter