SQL Indexes and Performance 2

Question 1

What is a query execution plan?

Accepted Answer

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

Answer

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?

Accepted Answer

EXPLAIN

Answer

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?

Accepted Answer

Reading every row in a table to find matching records

Answer

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?

Accepted Answer

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

Answer

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?

Accepted Answer

A boolean column with only TRUE or FALSE values

Answer

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.

SQL Practice Test

SQL Practice Test

SQL Indexes and Performance 2