Why aren't indexes always useful?

Indexes aren't always useful because, besides speeding up search, they add overhead to storing and updating data.

#The main reasons

1. They slow down write operations. On every INSERT, UPDATE, or DELETE, the database has to update not just the table itself but every related index. The more indexes there are, the slower changes run.
2. They take up extra space. Indexes are stored separately from the table and can take up a lot of space, especially when indexing large text or numeric fields.
3. They may go unused. If a query selects too much of the table (e.g. >20-30% of rows), the optimizer may decide a full scan is faster than going through the index.
4. They complicate optimization. With many indexes, the optimizer can pick a "bad" one, and the query ends up running even slower than without an index.
5. They're inefficient for frequently changing data. In tables with a high rate of updates, indexes fragment quickly, which reduces their benefit and requires periodic maintenance (rebuild/reorganize).

Summary: Indexes are useful only where reads are frequent and changes are rare, and where filters or sorts actually use the indexed fields. Otherwise, they slow the system down instead of speeding it up.