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.
15 cards · basic cards · AI-written, checked twice. Edit anything.
- What is an index, in a database?
- A data structure that improves the speed of data retrieval operations
- What is the main tradeoff of adding an index?
- Faster reads at the cost of slower writes and additional storage
- What is a B-tree index?
- A balanced tree structure commonly used for database indexing, supporting fast lookups and range queries
- What is a clustered index?
- An index that determines the physical storage order of table rows
- What is a non-clustered index?
- An index stored separately from the table data, containing pointers to the actual rows
- What is a composite index?
- An index built on more than one column
- What is query optimization?
- The process of choosing the most efficient way to execute a database query
- What is a query execution plan?
- A breakdown of the steps a database engine will take to run a query
- What is a full table scan?
- Reading every row in a table to find matching data, typically slower than using an index
- What is selectivity, in indexing terms?
- 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?
- It filters out relatively few rows, so scanning may be nearly as fast
- What is a covering index?
- An index that contains all the columns needed to satisfy a query without accessing the table
- What is query caching?
- Storing the results of a query to speed up repeated identical requests
- What is the EXPLAIN command used for?
- Showing how a database will execute a given query
- What is a hash index?
- An index using a hash function for very fast equality lookups, but not range queries
FlashKeepers