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
Top comments (6)
Database indexes become absolutely necessary when you scale past a certain point. We’ve added dozens over the past year based on the feedback from our MongoDB consultant as we invested heavily into performance optimizations for our application
It depends on your application, if you have a lot of readings it may make sense, it's not always that you need fast insertions
I did say specifically “when you scale past a certain point” not sure what contrast you’re trying to illustrate here
I think the question is not whether or not to use an index. The answer is from my POV "yes" in most scenarios. But how to keep your datastorage fast.
When you know the concepts of when not to use it is essential to keep your database fast, if in most scenarios you need an index on the other side you will lose speed/resources on other operations
You want an index if you don't want full table scans ;-)