What are indexes in a database? Why are they needed, when to use them, when not to?

Database indexes are special data structures that speed up searching and retrieving data from tables.

Purpose:

• Allow quick finding of needed records without scanning the entire table.
• Improve the performance of queries with WHERE, JOIN, ORDER BY conditions.

When to use:

• For columns that are frequently filtered or sorted.
• For key fields, such as primary keys.

When not to use:

• For columns with very high cardinality, where the index is ineffective.
• If the table is very small — an index may only slow down inserts and updates.
• If data changes frequently — indexes require additional time for updates.

Example of creating an index in SQL:

CREATE INDEX idx_user_email ON users(email);