Index types, query execution plans, and optimization techniques, each explained with a short example.
35 cards · basic cards · AI-written, checked twice. Edit anything.
- What is a database index?
Show answer
A data structure that improves query performance by enabling faster lookups and reducing the number of rows scanned.- What is the main difference between a clustered and non-clustered index?
Show answer
A clustered index determines the physical order of rows; a non-clustered index is a separate structure that points to rows.- How many clustered indexes can one table have?
Show answer
One. A table has at most one clustered index, usually on the primary key.- How many non-clustered indexes can a table have?
Show answer
Up to 999 in most SQL databases like SQL Server (varies by system).- What is a composite index?
Show answer
An index built on two or more columns, useful when queries filter on multiple fields together.- What is a query execution plan?
Show answer
A step-by-step blueprint showing how the database will retrieve and process query results, including costs and operations.- What does the EXPLAIN command show?
Show answer
The execution plan for a query, including access methods, estimated rows, and I/O costs.- What is a full table scan?
Show answer
Reading every row in a table sequentially to find matching records, the slowest access method.- When is a full table scan faster than using an index?
Show answer
When the query returns a large percentage of the table's rows, because index overhead outweighs the benefit.- What is an index seek?
Show answer
Directly navigating to matching rows using the index structure, without scanning unrelated rows.- What is an index scan?
Show answer
Reading all entries in an index from start to end, potentially without accessing the table for every row.- What is a covering index?
Show answer
An index that contains all columns needed to answer a query, eliminating the need to access the table.- Why use a covering index?
Show answer
It satisfies the entire query from the index alone, avoiding slower table lookups and improving performance.- What is a unique index?
Show answer
An index that enforces uniqueness for one or more columns, preventing duplicate values.- What is a primary key index?
Show answer
A unique, non-null index that uniquely identifies each row and is the main access point for the table.
FlashKeepers