SQL Indexes and Performance 2 — Questions and Answers
Question 1: What is a query execution plan?
- A schedule for running automated queries
- The sequence of steps the database engine uses to execute a query (Correct answer)
- A list of all indexes in a database
- A backup strategy for query results
Correct 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.
Question 2: Which keyword is used in MySQL and PostgreSQL to display a query's execution plan?
- ANALYZE
- EXPLAIN (Correct answer)
- DESCRIBE
- SHOWPLAN
Correct answer: EXPLAIN
The EXPLAIN keyword in MySQL and PostgreSQL displays the execution plan, revealing how the optimizer intends to process the query.
Question 3: What is a 'full table scan' in SQL?
- An index rebuild operation across the whole table
- Reading every row in a table to find matching records (Correct answer)
- A query that selects all columns with SELECT *
- A backup of the entire table's data
Correct 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.
Question 4: What is index selectivity?
- The ability to manually choose which index a query uses
- The ratio of unique values in an indexed column to the total number of rows (Correct answer)
- The order in which multiple indexes are applied to a query
- The physical size of an index measured in bytes
Correct 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.
Question 5: Which column type typically makes the WORST candidate for an index?
- A primary key column with millions of unique values
- A foreign key column used in JOIN conditions
- A boolean column with only TRUE or FALSE values (Correct answer)
- A datetime column used in range filters
Correct 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.
Question 6: What is the role of the query optimizer in a database system?
- A tool that rewrites SQL statements into stored procedures
- A component that evaluates multiple execution strategies and selects the most efficient plan (Correct answer)
- A process that compresses query results before returning them
- A feature that permanently caches the results of frequently run queries
Correct 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.
Question 7: What is index fragmentation?
- An index spread across multiple physical files
- An index with missing or corrupted entries
- Disorder in index pages caused by ongoing insertions, updates, and deletes (Correct answer)
- An index that still references deleted rows
Correct 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.
SQL Indexes and Performance 2
What is a query execution plan?