What are indexes in a database, when to use them, and when not to use them?

Indexes in a database are data structures that speed up the search and retrieval of data based on specific columns. They work like pointers, allowing the DBMS to quickly find the needed records without scanning the entire table.

When to use indexes:

• Frequently run queries with filtering on certain columns (WHERE, JOIN).
• Columns used for sorting (ORDER BY).
• Columns that are often involved in aggregate functions or groupings.

When not to use indexes:

• For columns with very high cardinality, where almost all values are unique, but queries on them are rare.
• For columns with very low cardinality (e.g., boolean values), where the index is ineffective.
• If the table is frequently updated, inserted into, or deleted from — indexes slow down these operations.

Proper use of indexes is a balance between speeding up reads and slowing down writes.