What is a database index and why does it matter?

Answer

An index is a separate data structure, usually a B-tree, that lets the database find rows matching a query without scanning the entire table. It dramatically speeds up reads on indexed columns, much like the index in the back of a book. The trade-off is that indexes consume storage and slow down writes, since every insert, update, or delete must also maintain the index. Choosing the right columns to index, and avoiding redundant indexes, is one of the most impactful database tuning tasks.