A database index is a separate, sorted data structure, typically a B-tree, that lets the database jump directly to matching rows instead of scanning every single row in a table to find them. A query optimiser then decides, separately for each individual query a database runs, whether using an available index is actually faster than simply reading the whole table in order, a decision that turns out to depend heavily on roughly how much of the table a given query actually needs to return.
An index trades faster reads for slower writes and extra storage
Building an index means maintaining a second, sorted structure alongside the table itself, every time a row is inserted, updated or deleted, that index has to be updated too, which makes writes measurably slower and adds genuine storage overhead. That trade-off is exactly why databases don't automatically index every column, a table that's read constantly but rarely written to benefits enormously from indexes, while a table under heavy, constant write load can actually slow down if it's carrying indexes it doesn't get enough read benefit from to justify the write cost.
The optimiser weighs an index's benefit against how selective the query actually is
For a highly selective query, one returning a small fraction of a table's rows, jumping straight to matching rows via an index is dramatically faster than reading the whole table. For a query returning a large fraction of the table, a full scan reading rows in their existing physical order can actually be faster than an index lookup, since jumping around to individually indexed rows carries its own random-access overhead. The query optimiser estimates which situation it's actually in using statistics it keeps about the table's data distribution, then picks the query plan its own estimate says will be fastest.
What we're still unsure about
That indexes speed up selective reads at the cost of slower writes, and that an optimiser's choice between using an index and scanning the full table depends on query selectivity, are well established, extensively documented facts of database engineering. What's more genuinely an open, practical problem is that query optimisers rely on statistical estimates of data distribution that can go stale over time or be systematically wrong for columns whose values are correlated with each other in ways the optimiser doesn't model, and experienced database engineers still routinely have to manually inspect and override an optimiser's chosen query plan, automatic query optimisation remains a genuinely imperfect, actively developed area of database engineering rather than a fully solved problem.
This sits inside Indexes & Query Optimisation, one of seven topics in Databases, one of seven domains in Computer Science, one of seventeen subjects the app can quiz you on.