Part 2 of 5 · 1 min read
Why an unindexed query is slow
With no index, the database must read every row to check a condition.
Think of searching for one specific name in an unsorted stack of a thousand index cards. There is no shortcut: every card must be checked.
In plain words: when a query filters on a column with no index, the database has only one option: read every row in the table, check the condition against each one, and keep the rows that match. This is called a full table scan. A full table scan's cost grows directly with the table's size, so a query that took a fraction of a second on a thousand rows can take minutes on a hundred million rows.
A worked example. Finding every order placed by one specific customer, with no index on customer_id, requires reading every row of the orders table and checking each one's customer_id value.
The key lines.
SELECT * FROM orders WHERE customer_id = 42;
-- with no index on customer_id: every row in orders is read and checked
Full table scan
A full table scan reads every row of a table to evaluate a query's condition, with cost proportional to the table's total size regardless of how few rows actually match.
Example: searching an unsorted stack of index cards one by one for a single name is a full table scan.