CSE Roadmap

Core tier · about 2 hours · 5 parts

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.