What is a database index and when does an index speed up a query?

An index is a separate structure that supports fast lookups and ordered scans for certain columns, similar to a book index. The most common is a B-tree (or B+-tree) index on a column used in WHERE, JOIN, or ORDER BY. Without a suitable index, the planner may do a full table scan, which is fine for small tables and terrible for big ones. Indexes are not free: INSERT/UPDATE/DELETE must maintain them, and wrong indexes bloat the database. In interviews, you connect EXPLAIN, selective predicates, and composite indexes (column order matters).

CREATE INDEX idx_orders_user_id ON orders (user_id);
-- helps: SELECT * FROM orders WHERE user_id = 42;

Start simple: try this concept in a tiny project before moving to advanced tools.