What does an index actually do, and what does it cost?
An index is a second copy of some of the data, kept in an order that lets the engine find the rows it wants without reading all of them.
So what does it actually do? A table is stored in whatever order the rows arrived in, so finding one row means reading every row until you reach it. An index is sorted, so the engine can look at the middle of it, decide which half to keep, and repeat that until it finds the one row that it wants. It is the same trick that you use to find a word in a dictionary.
CREATE TABLE orders (
id INTEGER PRIMARY KEY,
customer_id INTEGER NOT NULL,
amount DECIMAL(10, 2) NOT NULL
);
-- The index that the engine needs for this query.
CREATE INDEX idx_orders_customer ON orders (customer_id);
And what does it cost? Two things. It takes disk space, because it is a second copy of the data. And every time a row is written, the index has to be written too, so every insert, update and delete gets slower.
So an index is a trade. You give a little speed on the writes and you use some disk, and you get back a lot of speed on the reads that use it.
There is one more detail that is worth knowing. An index is only useful if the query can use it, and a condition can only use it when the column stands on its own. The condition WHERE customer_id = 42 can use the index. The condition WHERE customer_id + 0 = 42 cannot, because the engine has to work out the value for every row before it can compare it.
The next question in this topic shows the change that an index makes to the plan.