Database Systems, unit: Indexing and Query Optimization. Core concepts, terminology, and worked-example cues a college student meets for this unit, building on prior units without repeating them. Front: a term, concept, or short problem cue. Back: the definition, explanation, or answer.
About this set
38 basic cards for College Computer Science. Set 6 of 8 in Database Systems. It opens with "What is an index, in a database?" and "What is the main tradeoff of adding an index?". Study it alongside the other College sets below.
38 cards · basic cards · AI-written, checked twice. Edit anything.
- What is an index, in a database?
Show answer
A data structure that improves the speed of data retrieval operations- What is the main tradeoff of adding an index?
Show answer
Faster reads at the cost of slower writes and additional storage- What is a B-tree index?
Show answer
A balanced tree structure commonly used for database indexing, supporting fast lookups and range queries- What is a clustered index?
Show answer
An index that determines the physical storage order of table rows- What is a non-clustered index?
Show answer
An index stored separately from the table data, containing pointers to the actual rows- What is a composite index?
Show answer
An index built on more than one column- What is query optimization?
Show answer
The process of choosing the most efficient way to execute a database query- What is a query execution plan?
Show answer
A breakdown of the steps a database engine will take to run a query- What is a full table scan?
Show answer
Reading every row in a table to find matching data, typically slower than using an index- What is selectivity, in indexing terms?
Show answer
A measure of how many distinct values a column has relative to total rows- Why is an index less useful on a low-selectivity column?
Show answer
It filters out relatively few rows, so scanning may be nearly as fast- What is a covering index?
Show answer
An index that contains all the columns needed to satisfy a query without accessing the table- What is query caching?
Show answer
Storing the results of a query to speed up repeated identical requests- What is the EXPLAIN command used for?
Show answer
Showing how a database will execute a given query- What is a hash index?
Show answer
An index using a hash function for very fast equality lookups, but not range queries
FlashKeepers