Query Optimization and Performance Tuning

The SQL query does not need to change after the index is created.

Programming

Interactive lab

Try it: Index vs Full Table Scan

Why an index speeds up a lookup: without one the database must scan and test every row; with CREATE INDEX it binary-searches a sorted list of (key, rowid) entries and reads only the matching rows.

How it works

No index on the searched column: SCAN the table, testing every row.
CREATE INDEX keeps (key, rowid) entries sorted by key in a separate B-tree.
A lookup binary-searches the entries for the first key ≥ the target, halving the range each probe.
It then walks forward while keys still qualify, fetching each matching row by its rowid, and stops at the first key past the target — sorted order means nothing later can match.

Default run (11 steps): SELECT * FROM people WHERE age = 27. Query plan: SEARCH people USING INDEX idx_people_age (age=?). … 3 matching rows, returned in index order. Read 3 table rows + 8 index entries (a full scan reads 12).

Simplified: A toy relational engine in the browser, not SQLite itself: a small structured query model built from the controls (no SQL text is parsed or executed), typed INTEGER/TEXT/NULL values, and tables of at most a few rows. Results were checked against Python's sqlite3 (SQLite 3.49). The B-tree is drawn as its sorted leaf level, and binary search stands in for descending the tree. Counts are rows and entries touched, not disk pages. The query plan text is what SQLite's EXPLAIN QUERY PLAN reports for the same query.

Educational simulation

Loading the simulation…

The Lookup Problem

Imagine a music database containing millions of artist records. A user asks for all tracks by Frank Sinatra. The database must find the matching row or rows in the Artist table and then use those results to find related rows in the Track table. The central performance question is how the database finds the artist: does it inspect every row, or can it move directly toward the matching value?

What happens to lookup performance when the database searches every row compared with using an index?

Without an index, the database performs a full table scan. It starts at the first row and checks rows one by one until it has found the relevant matches. With a large table, this may require examining millions of rows. An index provides extra information that lets the database locate matching rows by using the searched column value instead of checking every row.

Index Mapping

Definition

An index is extra information stored by a database to enable faster lookups on a specific column without scanning every row.

An index does not store a complete copy of all table data. Instead, it stores a mapping from values in the indexed column to the locations or row identifiers of rows containing those values. When the database searches for a value such as Frank Sinatra, the index helps it find the relevant row location, after which the database retrieves the full row.

How does a database move from a searched column value to the matching row without scanning the entire table?

For a unique or rare value, an index can reduce the search from checking millions of rows to examining only the relevant rows. The improvement is most useful when the table is large and the search returns a small fraction of its rows.

Logical Keys Worth Indexing

Definition

A logical key is a text column that represents a real-world identifier and is used in WHERE clauses to find rows. Examples include an artist name or an email address.

In a music database, Artist.name is a logical key because people naturally identify and search for artists by name. In a user database, an email address can be a logical key when users log in using that address. Logical keys differ from primary keys, which are usually numeric IDs assigned by the database: a logical key carries meaning for people and commonly reflects how applications search for records.

How is a logical key connected to a table column, and why does indexing that column make lookups more efficient?

Best practice

Consider indexing a logical key when the table is large, the column is frequently used in WHERE clauses, the column contains text, and searches usually return a small fraction of the table. These conditions make it more likely that avoiding a full table scan will provide a meaningful benefit.

Creating the Index

The SQL CREATE INDEX statement specifies three essential choices: the name of the index, the table being indexed, and the column on which the index is created. Choose an index name that clearly reflects the table and column.

sql

CREATE INDEX artist_name
ON Artist (name);

CREATE INDEX artist_name
ON Artist (name);

Here, artist_name is the index name, Artist is the table, and name is the indexed text column. Once the statement executes, the database builds the index structure for Artist.name.

Worked example

Indexing a User Email

A user database frequently searches for accounts using the email column. Write a CREATE INDEX statement for that text column.

1
Name the index
Use a descriptive name that identifies the table and column, such as user_email.
2
Name the table
Place the user table after ON.
3
Name the column
Place email inside parentheses because it is the column being indexed.

Result

CREATE INDEX user_email
ON User (email);

Transparent Query Improvement

The SQL query does not need to change after the index is created. A query that searches Artist.name can remain the same. The database automatically uses the index when it is appropriate, allowing the query to locate the matching artist rows more efficiently before joining them to Track.

How does the database receive the same query, choose an index, and retrieve matching rows without changes to the SQL?

sql

SELECT Track.title, Track.play_count, Artist.name
FROM Track
JOIN Artist ON Track.artist_id = Artist.id
WHERE Artist.name = 'Frank Sinatra';

SELECT Track.title, Track.play_count, Artist.name
FROM Track
JOIN Artist ON Track.artist_id = Artist.id
WHERE Artist.name = 'Frank Sinatra';

With no index on Artist.name, the database must scan the Artist table to find rows whose name equals Frank Sinatra. With the index, it can locate the matching artist row or rows and then perform the join. The SELECT statement remains unchanged; the performance improvement comes from the database structure and its query execution choices.

Keeping Indexes Current

The database automatically maintains an index as table data changes. When indexed data changes, the database keeps the index's value-to-row mapping aligned with the table. This means applications do not need to manually rebuild the mapping for ordinary data changes.

What changes in the index when a row is inserted, updated, or deleted from the table?

Common Indexing Mistakes

Common mistake
Assuming an index requires a different SELECT statement.
What it looks like
Rewriting the query to use special index syntax after creating an index on Artist.name.
Why it fails
The database uses the index transparently for suitable searches, so the existing query syntax does not need to change.
Do this instead
Create the index and keep using the normal query that searches the indexed column.
Common mistake
Indexing every column.
What it looks like
Adding indexes to all columns in a small table even though most columns are rarely used in WHERE clauses.
Why it fails
Indexes consume storage and create maintenance overhead, while a rarely searched column may provide little lookup benefit.
Do this instead
Prioritize large tables and frequently searched, relatively selective logical keys.
Common mistake
Confusing a logical key with a database-assigned primary key.
What it looks like
Treating a numeric database ID and a meaningful artist name as the same kind of identifier.
Why it fails
A logical key represents how people naturally identify and search for records, while primary keys are usually numeric IDs assigned by the database.
Do this instead
Look for meaningful text columns used in WHERE clauses, such as artist names or email addresses.
Common mistake
Expecting an index to copy the whole table.
What it looks like
Assuming the index contains every complete Artist row.
Why it fails
An index stores a mapping from indexed values to row locations rather than a complete copy of all table data.
Do this instead
Think of the index as a route to matching rows; the database retrieves the full row separately.

Practice the Decision

EASY

A library database has a large Book table. Applications frequently search for books using the text column isbn_label in a WHERE clause, and most searches return only a small fraction of the table. Decide whether this column is a good indexing candidate and write a CREATE INDEX statement for it.

Hints

Check whether the table is large.
Check whether the column is frequently searched in a WHERE clause.
Check whether searches return a small fraction of rows.
Use the pattern CREATE INDEX index_name ON table_name (column_name);

Your attempt (not saved)

Worked example

Practice Answer

Evaluate the isbn_label column and write the index statement.

1
Evaluate the table
The Book table is large, so avoiding a full table scan can matter.
2
Evaluate the search
The column is frequently used in WHERE clauses, and searches return a small fraction of rows. These are conditions that favor an index.
3
Write the statement
Give the index a descriptive name and place the table and column in the appropriate parts of the CREATE INDEX statement.

Result

CREATE INDEX book_isbn_label
ON Book (isbn_label);

Key Takeaways

1
An index stores extra information that maps values in a column to row locations, helping the database avoid checking every row.
2
A full table scan checks rows one by one and can be slow for large tables.
3
Logical keys are meaningful identifiers such as artist names or email addresses that are commonly used in WHERE clauses.
4
CREATE INDEX specifies an index name, a table, and the column to index.
5
The database maintains and uses indexes automatically, but indexes should be added selectively because they consume storage and create maintenance overhead.

At a glance

Indexes improve lookups by mapping searched column values to matching row locations.
They are especially useful for large tables with frequently searched, relatively selective logical keys.
CREATE INDEX adds an index without requiring changes to existing query syntax.
The database automatically maintains and applies indexes, while consuming additional storage and maintenance effort.

Related concepts

More concepts starting with Q

Try it: Index vs Full Table Scan

The Lookup Problem

Index Mapping

Logical Keys Worth Indexing

Creating the Index

Transparent Query Improvement

Keeping Indexes Current

Common Indexing Mistakes

Practice the Decision

Key Takeaways

At a glance

Related Topics

Related concepts