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 schedule for running automated queries

Answer

A list of all indexes in a database

Answer

A backup strategy for query results

Question 2

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

Accepted Answer

EXPLAIN

Answer

ANALYZE

Answer

DESCRIBE

Answer

SHOWPLAN

Question 3

What is a 'full table scan' in SQL?

Accepted Answer

Reading every row in a table to find matching records

Answer

An index rebuild operation across the whole table

Answer

A query that selects all columns with SELECT *

Answer

A backup of the entire table's data

Question 4

What is index selectivity?

Accepted Answer

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

Answer

The ability to manually choose which index a query uses

Answer

The order in which multiple indexes are applied to a query

Answer

The physical size of an index measured in bytes

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 primary key column with millions of unique values

Answer

A foreign key column used in JOIN conditions

Answer

A datetime column used in range filters

Question 6

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

Accepted Answer

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

Answer

A tool that rewrites SQL statements into stored procedures

Answer

A process that compresses query results before returning them

Answer

A feature that permanently caches the results of frequently run queries

Question 7

What is index fragmentation?

Accepted Answer

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

Answer

An index spread across multiple physical files

Answer

An index with missing or corrupted entries

Answer

An index that still references deleted rows