DEV Community

Cover image for Should I use database index or not?
Emanuel Ferreira
Emanuel Ferreira

Posted on

Should I use database index or not?

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

Oldest comments (6)

Collapse
 
thomasjunkos profile image
Thomas Junkツ

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.

Collapse
 
emanuelferreira profile image
Emanuel Ferreira

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

Collapse
 
theaccordance profile image
Joe Mainwaring

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

Collapse
 
emanuelferreira profile image
Emanuel Ferreira

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

Collapse
 
theaccordance profile image
Joe Mainwaring

I did say specifically “when you scale past a certain point” not sure what contrast you’re trying to illustrate here

Collapse
 
leob profile image
leob

You want an index if you don't want full table scans ;-)