What is an index in a relational DBMS? Why is it needed? When should it not be used?

An index in a relational DBMS is a data structure that speeds up searching and sorting records in a table by one or more columns. Indexes allow quick access to the needed rows without scanning the entire table.

Why an index is needed:

• Accelerates SELECT operations with WHERE, JOIN, ORDER BY conditions.
• Improves performance for queries on indexed fields.

When not to use an index:

• On very small tables — the overhead of maintaining the index may outweigh the benefits.
• On columns with very high cardinality, where values often repeat (e.g., boolean fields).
• If the table is frequently updated, inserted into, or deleted — indexes slow down these operations as they require additional updates.

Example: if you have a users table with an email field, and you often search for a user by email, it is worth creating a unique index on email. But if you have a "gender" field with only two values, an index on it will be less effective.

Thus, indexes are a powerful tool for read optimization but require a balanced approach considering the nature of the data and load.