Indexes and Performance Flashcards

What is a query execution plan?

Answer: The sequence of steps the database engine uses to execute a query

A query execution plan describes the operations (scans, seeks, joins, sorts) the database engine will perform to retrieve or modify data for a given query.

Which keyword is used in MySQL and PostgreSQL to display a query's execution plan?

Answer: EXPLAIN

The EXPLAIN keyword in MySQL and PostgreSQL displays the execution plan, revealing how the optimizer intends to process the query.

What is a 'full table scan' in SQL?

Answer: Reading every row in a table to find matching records

A full table scan reads every row in the table to find matching records, which is inefficient on large tables that lack a useful index.

What is index selectivity?

Answer: The ratio of unique values in an indexed column to the total number of rows

Index selectivity measures how many unique values exist relative to total rows; higher selectivity (more distinct values) makes an index more efficient at filtering rows.

Which column type typically makes the WORST candidate for an index?

Answer: A boolean column with only TRUE or FALSE values

A boolean column with only two distinct values has very low selectivity, so the index eliminates very few rows and the optimizer often ignores it in favor of a table scan.

What is the role of the query optimizer in a database system?

Answer: A component that evaluates multiple execution strategies and selects the most efficient plan

The query optimizer analyzes possible execution plans (different index choices, join orders, etc.) and selects the one estimated to be most efficient.

What is index fragmentation?

Answer: Disorder in index pages caused by ongoing insertions, updates, and deletes

Index fragmentation occurs when frequent data modifications cause index pages to fall out of logical order, increasing I/O and degrading query performance.