Indexes are most commonly related to improving database speed, query efficiency and lowering latency. As in wikipedia
A database index is a data structure that improves the speed of data retrieval operations on a database table at the cost of additional writes and the use of more storage space to maintain the extra copy of data. Indexes are used to quickly locate data without having to search every row in a database table every time a database table is accessed. Indexes can be created using one or more columns of a database table, providing the basis for both rapid random lookups and efficient access of ordered records.
But this is very general observation, indexes can give you hedache as your data grows. This is how?
1.) Indexes does not scale as your data grows
- First, memory is limited and indexes are loaded into memory as soon as you start database instance. As data grows, their would be a point where memory gets exausted.
- Second, DB writes becomes costlier as data grows vertically(no of rows).Everytime data gets inserted, indexes get adjusted; that means as data grows, it slows down write operation.
2.) No of indexes invertly related to horizontal scaling
Yes, If one try to index most attributes in given table, one might screw up database in long run. More number of indexes per table means more memory uses and less efficient write operations would be
Solution
1.) Do not do early optimisation
Early or premature optimisation is a killer. Do not guess what should be indexed, what not, instead wait and watch and act accordingly
2.) Index wisely
Do consider trade offs while considering an attribute to index. Look for all edge cases. If you are indexing multiple attributes in single table, it is an indication that something is wrong.
Disclaimer: This applies to all databases out there, sql or nosql
Happy Indexing :)
About The Author
I am Pankaj Baagwan, a System Design Architect. A Computer Scientist by heart, process enthusiast, and open source author/contributor/writer. Advocates Karma. Love working with cutting edge, fascinating, open source technologies.
To consult Pankaj Bagwan on System Design, Cyber Security and Application Development, SEO and SMO, please reach out at me[at]bagwanpankaj[dot]com
For promotion/advertisement of your services and products on this blog, please reach out at me[at]bagwanpankaj[dot]com
Stay tuned <3. Signing off for RAAM