Query Optimization and Performance Tuning

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?

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

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.

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

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.

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.

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.

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.

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.

Common Indexing Mistakes

• Assuming an index requires a different SELECT statement.

  The database uses the index transparently for suitable searches, so the existing query syntax does not need to change.

  Fix: Create the index and keep using the normal query that searches the indexed column.

• Indexing every column.

  Indexes consume storage and create maintenance overhead, while a rarely searched column may provide little lookup benefit.

  Fix: Prioritize large tables and frequently searched, relatively selective logical keys.

• Confusing a logical key with a database-assigned primary key.

  A logical key represents how people naturally identify and search for records, while primary keys are usually numeric IDs assigned by the database.

  Fix: Look for meaningful text columns used in WHERE clauses, such as artist names or email addresses.

• Expecting an index to copy the whole table.

  An index stores a mapping from indexed values to row locations rather than a complete copy of all table data.

  Fix: Think of the index as a route to matching rows; the database retrieves the full row separately.

Practice the Decision

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.