CREATE INDEX: Enhancing Data Retrieval with Indexing in SQL

An index is a separate, sorted structure the database maintains alongside your table so it can find rows instead of searching for them. Without one, answering WHERE city = 'Austin' means reading every single row and checking — a full table scan. With one, the database jumps almost straight to the matches.

That’s the claim. Here it is happening. Press Run query with the index on, then uncheck it and run again — watch how many rows light up:

With no index the database touches all 24 rows to find a handful. With the index it binary-searches the sorted keys — about 5 probes — then walks forward through the matches. On 24 rows that’s a curiosity. On 24 million rows it’s the difference between a query that returns instantly and one that times out.

The syntax

CREATE INDEX idx_customers_city ON customers (city);

The name is yours (a convention like idx_table_column pays off later); the table and column list say what to index. Variants you’ll actually use:

-- Enforce uniqueness as well as speed lookups
CREATE UNIQUE INDEX idx_customers_email ON customers (email);

-- A composite index across two columns, in this order
CREATE INDEX idx_orders_cust_date ON orders (customer_id, order_date);

-- Remove one (syntax varies — see below)
DROP INDEX idx_customers_city;

Dialect note: CREATE INDEX ... ON table (cols) is consistent everywhere, but DROP INDEX genuinely diverges — the bare form above works in PostgreSQL, Oracle, and SQLite (index names are schema-scoped there), while MySQL needs the table (DROP INDEX idx_name ON customers) and SQL Server wants DROP INDEX idx_name ON customers. SQL Server also accepts the older DROP INDEX customers.idx_name, but Microsoft documents that form as deprecated — prefer the ON syntax. Note too that an index backing a PRIMARY KEY or UNIQUE constraint usually can’t be dropped directly; you drop the constraint instead.

Why it’s fast: O(n) vs O(log n)

Most indexes are B-trees — a balanced, sorted tree. Finding a value means descending from the root, and each step discards a large fraction of what’s left, so the work grows with the logarithm of the row count instead of the row count itself.

The practical version is even better than “logarithmic” suggests. A B-tree node is a whole page holding hundreds of keys, so the fan-out is huge and real trees are astonishingly shallow:

Multiply the row count by a thousand and the scan gets a thousand times slower, while the index adds roughly one more level. That asymmetry is the entire reason indexes exist.

Because a B-tree is sorted, fetching a range costs about O(log n + k) — one descent to the first matching key, then a walk along the leaves for the k matches. (The visualizer above shows this in miniature: a few probes to locate 'Austin', then a short walk through the matches.)

That sortedness is also why one index serves so many query shapes: range queries (WHERE price BETWEEN 20 AND 50), prefix matches (LIKE 'Aus%'), and often ORDER BY — the engine can read rows in index order instead of sorting them.

You also rarely need to index a primary key or a unique column yourself: every major database creates that index automatically when you declare the constraint. The genuinely under-indexed columns are usually foreign keys, which get no automatic index in most engines and are exactly what joins filter on.

Composite indexes and the leftmost-prefix rule

An index on (customer_id, order_date) is sorted by customer_id first, then by order_date within each customer. That means it helps queries that filter on:

• customer_id alone ✅
• customer_id and order_date ✅
• order_date alone ❌ — no seek is possible here, because the dates are only sorted within each customer (an engine may still scan the whole index as a skinnier stand-in for the table, but that’s not the win you wanted)

This is the leftmost-prefix rule: a composite index serves queries that use a prefix of its column list, starting from the left. Column order is a design decision, not an afterthought.

When an index won’t help

Indexes aren’t free and aren’t always used:

• Wrapping the column in a function defeats it. WHERE LOWER(name) = 'ava' can’t use a plain index on name, because the index stores name, not LOWER(name). Fix it with an expression index (CREATE INDEX ... ON customers (LOWER(name))) — supported in PostgreSQL, Oracle, SQLite, and MySQL 8.0.13+, though SQL Server has no direct equivalent and wants a PERSISTED computed column with an index on it — or by storing a normalized column — the same normalization idea behind fuzzy joins.
• Leading wildcards defeat it. LIKE 'Aus%' can use an index; LIKE '%tin' generally cannot, because you can’t binary-search on an unknown beginning.
• Low selectivity makes it pointless. An index on a boolean or a status with three values usually isn’t worth it — if a value matches 40% of the table, reading the table directly is cheaper than bouncing between index and rows.
• Writes get slower. Every INSERT, UPDATE, and DELETE must maintain every affected index. Indexes are a read/write trade, and indexing “just in case” is how write-heavy tables get slow.

Verify, don’t guess

Whether an index is actually used is a question for the query planner, not intuition:

EXPLAIN SELECT * FROM customers WHERE city = 'Austin';         -- the estimated plan
EXPLAIN ANALYZE SELECT * FROM customers WHERE city = 'Austin'; -- plan + what really happened

The distinction that matters is estimated vs actual. Plain EXPLAIN (and Oracle’s EXPLAIN PLAN) shows what the optimizer expects; EXPLAIN ANALYZE — available in PostgreSQL and MySQL 8.0.18+ — and SQL Server’s “actual execution plan” show what really happened. A big gap between estimated and actual rows usually means stale statistics.

⚠️ EXPLAIN ANALYZE actually executes the statement. That’s fine for a SELECT; wrap it in a transaction you roll back before pointing it at anything that writes.

Look for a seek — a targeted descent — rather than a sequential / full table scan. Read the operator names carefully: SQL Server’s “Index Scan” reads the entire index and is closer to a scan than a seek, whereas its “Index Seek” is the one you want (Seq Scan in PostgreSQL, type: ALL in MySQL, TABLE ACCESS FULL in Oracle). If you added an index and the plan didn’t change, something above — a function on the column, a type mismatch, or poor selectivity — is stopping it. And a full scan isn’t automatically a mistake: when a predicate matches a large share of the table, or the table is tiny, scanning really is cheaper.

Frequently asked questions

What does CREATE INDEX do? It builds a sorted auxiliary structure (usually a B-tree) on one or more columns so the database can locate matching rows without reading the whole table.

Do indexes make everything faster? No. They speed up reads that can use them and slow down writes, since every index must be kept current. They also consume storage.

What is a composite index? An index over several columns in a defined order. It serves queries filtering on a leftmost prefix of those columns — (a, b) helps a and a, b, but not b alone.

Why isn’t my index being used? Common causes: a function applied to the column, a leading % wildcard, a data-type mismatch, or a column so unselective that a scan is genuinely cheaper. Check with EXPLAIN.

How many indexes should a table have? Enough to serve its real query patterns and no more. Each one adds write cost, so index the columns you actually filter, join, and sort on.

Indexing is one lever of many

Making data fast is rarely just indexes — it’s also how the data is shaped, moved, and refreshed. ET1 handles the moving and shaping half: a visual, asynchronous ETL canvas where filters and joins run as nodes with live previews, so you can push work upstream instead of asking one enormous query to do everything.

Keep learning: normalization (the shape of the data), views, joins, and optimizing analytical queries.