Database Indexing

Indexing is always the most crucial part of a database design where you have to trade-off between write operation speeds and read/update/delete speeds. While deciding the type of indexes, one must always consider several factors like the kind of expected queries, read-to-write ratio, amount of memory in your system, size of your database etc.

While coming up with an indexing strategy, one must always have a deep understanding of the application's queries and what are the most frequently referred fields. Relative frequency of each query justifies whether it even needs an index or not.

Before using the strategy in production, indexes must be designed and tested over with different configurations in the dev-environment itself to inspect which strategy performs the best.
Inspect the current indexes created for your collections to ensure they are supporting your current and planned queries. If an index is no longer used, drop the index.

The following are some indexing strategies that can come handful while designing your database structure :

Indexing for your queries

An index supports a query when the index contains all the fields scanned by the query. Creating indexes that support queries results in greatly increased query performance.

Indexing for sorting related queries

One can support efficient queries, by using the strategies here while specifying the sequential order and sort order of index fields.

Ensuring that indexes fit in RAM

When the index fits in RAM, one can avoid reading the indexes from the disk and get faster processing speeds.

Create Queries that Ensure Selectivity

Selectivity is the ability of a query to narrow results using the index. Selectivity allows databases to use indexes for a larger portion of the work associated with fulfilling the query.

Different types of indexes available -

1. Single Field
2. Compound Indexing (Using multiple fields to create single index)
3. Text Indexing
4. Partial Indexing (Indexing a field based on a condition)
5. Sparse Indexing (Ensures that the each row in database contains the index or else the row is skipped)
6. TTL Indexing (Special indexing that can remove rows after certain amount of time)

A combination of the above mentioned indexes can easily enhance the processing speeds of your queries.