Tell me about indexes: when to build them, when not to, and what types exist.

Indexes in databases are data structures that speed up searching and data retrieval based on specific columns.

When to build indexes:

• If search, filtering, or sorting operations are frequently performed on a column.
• For columns involved in JOINs.
• For unique fields to ensure uniqueness and accelerate access.

When not to build indexes:

• On columns with very high cardinality, where almost all values are unique, but queries on them are rare.
• On columns with frequent insert/update operations, as indexes slow down these operations.
• If the table is very small — an index may not provide a performance gain.

Types of indexes:

• B-tree index — the most common, suitable for equality, range, and sorting operations.
• Hash index — fast for exact key lookups but does not support range queries.
• Bitmap index — effective for columns with low cardinality (e.g., gender).
• Full-text index — for text search.

Example: if you have a users table with an email field, it is advisable to create a unique B-tree index on email for quick searches and to ensure uniqueness.

It is important to balance the number and types of indexes to avoid degrading write performance and to prevent occupying unnecessary space.