liveBook · Manning

Standard index

As explained in Grokking Relational Database Design

A standard index is a database index that accelerates operations such as sorting and exact-value matching.

A standard index adds a data structure that helps a database find rows by column values and avoid repeatedly sorting an entire table. B-trees commonly maintain indexed values in sorted order. section 7.9

Speeding up a movie listing

Suppose users usually want the highest-rated movies. Sorting millions of rows before returning 30 results can be expensive. Add an index to the rating column, then keep the query: SELECT * FROM movie ORDER BY rating DESC LIMIT 30;. The same index can support an exact-value lookup such as WHERE rating = 5. section 7.9

How Grokking Relational Database Design covers it

How deep it goes
Explains query ordering, repeated sorting costs, index creation, and B-tree access without developing broader index design theory.
Examples
Uses a movie table, rating sort, LIMIT 30 query, rating index, and rating-equality query.
What it assumes
Assumes familiarity with basic SELECT queries and introduces indexing rationale, query order, and B-tree terminology.
What you can do after
Apply it via SQL by creating a column index for a recurring sort or exact-value query.
How it connects
Connects standard indexes to B-trees, ORDER BY, exact-value matching, clustered indexes, and full-text indexes.
Cover of Grokking Relational Database Design
liveBookmanning.com
Read Grokking Relational Database Design on liveBook Get the book at manning.com

Related concepts

This explainer was generated by Manning's knowledge graph from the text of Grokking Relational Database Design. The section links point to the parts of the book it draws on.