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?
- 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?
- 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?
- One. A table has at most one clustered index, usually on the primary key.
- How many non-clustered indexes can a table have?
- Up to 999 in most SQL databases like SQL Server (varies by system).
- What is a composite index?
- An index built on two or more columns, useful when queries filter on multiple fields together.
- What is a query execution plan?
- 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?
- The execution plan for a query, including access methods, estimated rows, and I/O costs.
- What is a full table scan?
- 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?
- When the query returns a large percentage of the table's rows, because index overhead outweighs the benefit.
- What is an index seek?
- Directly navigating to matching rows using the index structure, without scanning unrelated rows.
- What is an index scan?
- Reading all entries in an index from start to end, potentially without accessing the table for every row.
- What is a covering index?
- An index that contains all columns needed to answer a query, eliminating the need to access the table.
- Why use a covering index?
- It satisfies the entire query from the index alone, avoiding slower table lookups and improving performance.
- What is a unique index?
- An index that enforces uniqueness for one or more columns, preventing duplicate values.
- What is a primary key index?
- A unique, non-null index that uniquely identifies each row and is the main access point for the table.