What is a database index?

A database index is an extra data structure, kept sorted by one or more columns, that points to the rows of a table. With an index on email, a query WHERE email = ? jumps to the matching entries instead of scanning every row. Most engines use B-trees by default, which also serve range conditions (<, BETWEEN) and ORDER BY.

Before and after, in the query plan

python - <<'EOF'
import sqlite3
db = sqlite3.connect(":memory:")
db.execute("create table users (id integer primary key, email text, country text)")
db.executemany("insert into users (email, country) values (?, ?)",
               [(f"user{i}@example.com", "IT" if i % 2 else "DE") for i in range(10000)])
plan = lambda sql: [row[-1] for row in db.execute("explain query plan " + sql)]
q = "select id from users where email = 'user42@example.com'"
print("without index:", plan(q))
db.execute("create index users_email on users (email)")
print("with index:   ", plan(q))
EOF

without index: ['SCAN users']
with index:    ['SEARCH users USING COVERING INDEX users_email (email=?)']

SCAN users reads the whole table; SEARCH users USING INDEX users_email (email=?) looks the value up in the index.

Trade-offs

Check what the engine actually does with EXPLAIN QUERY PLAN (SQLite), EXPLAIN ANALYZE (PostgreSQL) or EXPLAIN (MySQL) instead of adding indexes by guesswork.