PC 318 - Midterms - Logan

wHattTT

What is an execution plan?

The roadmap (step-by-step route) the database chooses to find the data a SQL query asks for.

Which database component chooses the execution plan?

The query optimizer.

In the GPS analogy, what does the SQL query represent?

The destination (what you want).

In the GPS analogy, what does the execution plan represent?

The route (how to get there).

In the GPS analogy, what does the optimizer represent?

The GPS that chooses the route.

What are the five stages a SQL query goes through from request to result?

Parsing, Optimization, Execution plan, Execution, Result.

What happens during the parsing stage?

The database reads the request and checks it.

What happens during the optimization stage?

The optimizer considers possible routes and chooses one.

What happens during the execution stage?

The database follows the chosen route (the execution plan).

When is an estimated execution plan produced?

Before the query runs.

What does an estimated execution plan show?

A prediction of what may happen, based on information such as indexes and statistics.

When is an actual execution plan shown?

After the query runs.

Which plan shows what really happened, including runtime and actual row information?

The actual execution plan.

What is a clue that something is wrong when comparing the two plans?

A big gap between the expected (estimated) rows and the actual rows.

What is the SSMS shortcut to include the actual execution plan?

Ctrl + M

What is the SSMS shortcut to display the estimated execution plan?

Ctrl + L

What is the SSMS menu path for including the actual execution plan?

Query > Include Actual Execution Plan

What does SSMS stand for?

SQL Server Management Studio.

Which keyword placed before a query shows its plan in MySQL and PostgreSQL?

EXPLAIN

What four things does EXPLAIN help you see?

The plan, whether indexes are used, the rows, and the operations.

Do the exact plan-viewing commands depend on the DBMS?

Yes, the exact command or button depends on the database system.

In an execution plan, what is an operator?

A step the database performs.

In an execution plan, what does "rows" refer to?

How many records are handled.

In an execution plan, what does "cost" mean?

A relative estimate of the work.

What does a Table scan operator do?

Looks through rows one by one.

What does a Filter operator do?

Keeps only the matching rows (e.g., grade >= 90).

What does a Hash match operator do?

Combines related rows (a join), e.g., connecting Students and Grades by student_id.

What does a Sort operator do?

Puts rows in order.

What does a Result operator do?

Gives the requested data.

What is a query bottleneck?

The step that makes the whole query slower.

What is the classroom analogy for a bottleneck?

30 students leaving through one narrow door; the door is the bottleneck.

What is a database example of a bottleneck?

Reading 1,000,000 rows just to return 10 rows.

What are four common causes of a bottleneck?

Full scan, inefficient JOIN, missing or poor index, too much sorting.

What problem does a full scan cause?

Too many records are checked.

What problem does an inefficient JOIN cause?

Too much data is combined.

What problem does a missing or poor index cause?

Rows are harder to find.

What problem does too much sorting cause?

A large list must be ordered.

Is a table scan always bad?

No. It can be fine for a small table or when most rows are needed.

Which columns are good candidates for indexes?

Columns often used in WHERE or JOIN.

Write the SQL to create an index named idx_grades_student on student_id of the grades table.

CREATE INDEX idx_grades_student ON grades(student_id);

What is the downside of having too many indexes?

They use storage and can make INSERT/UPDATE work harder.

What does SELECT * do?

Returns every column of the table.

Why should you avoid SELECT * when you need only a few columns?

It wastes memory, I/O, and network work; ask only for the columns you need.

Why is WHERE YEAR(grade_date) = 2025 less index-friendly?

The function is applied to every date, which can make efficient index use harder.

What is a more index-friendly way to filter grades for 2025?

A range condition: WHERE grade_date >= '2025-01-01' AND grade_date < '2026-01-01'

What should you always do to verify a rewritten WHERE condition helps?

Check the actual execution plan.

What join problems should you watch for?

Large tables, weak join conditions, joining many rows before filtering, and missing or unsuitable indexes.

What is the N+1 query problem?

One query gets a list (e.g., 100 students) and then N more queries fetch related rows for each one (101 database calls).

What is the fix for the N+1 problem?

A set-based approach: one JOIN query that gets the related data together.

Why can a leading wildcard such as LIKE '%ana' hurt performance?

It may make efficient index use harder.

What is the difference between UNION and UNION ALL?

UNION removes duplicates (extra work); UNION ALL does not, so use it when duplicates are not a concern.

What is the better habit for excessive rows?

Filter when appropriate so fewer records are processed.

What is the better habit for unnecessary JOINs?

Connect only the tables you actually need.

What is the better habit for repeated calls to the database?

Avoid unnecessary round trips.

What are the steps of the query optimization loop?

Write query, View plan, Analyze, Find bottleneck, Optimize, Test again.

What is the rhythm of query optimization?

Optimize, Measure, Repeat.

If a query is slow, should you add an index immediately?

Not always. First check the execution plan and find the bottleneck.

What fixes might a slow query need besides an index?

A clearer WHERE condition, fewer columns, a better JOIN, or less data to process.

What is the key rule when tuning a slow query?

Find the problem, make a change, test again. Don't guess; check the plan.

What is the best first step when teachers often search WHERE student_id = ? on a large grades table?

Investigate an index on student_id and verify with the execution plan.

What is database indexing?

A data structure that helps a database find and retrieve data faster by reducing the amount of data it needs to examine.

What is the book analogy for an index?

Like a book index, it lets you jump straight to the page without reading the whole book.

How does a query run without an index?

It checks Row 1, Row 2, Row 3 and so on until it finds a match (full table scan).

How does a query run with an index?

Query goes to the index, which leads to the target record.

What is a single-column index?

An index that uses one column (e.g., student_id).

What is a composite index?

An index that uses two or more columns (e.g., (course, year_level)).

What is a unique index?

An index that prevents duplicate values in the indexed key.

What is a covering index?

An index that contains all the columns needed by a particular query.

Write the SQL for a composite index named idx_student on course and year_level of Students.

CREATE INDEX idx_student ON Students(course, year_level);

Does column order matter in a composite index?

Yes. Column order affects how useful the index is.

What is the benefit of a covering index?

The engine may answer the query directly from the index without visiting the base table, reducing extra table lookups.

Write the SQL for a covering index named idx_cover for SELECT student_id, name FROM Students WHERE student_id = 1005.

CREATE INDEX idx_cover ON Students(student_id, name);

Do exact index types and behavior depend on the DBMS?

Yes, they depend on the DBMS and the query optimizer.

When should you NOT create an index?

Very small tables, rarely queried columns, columns with little filtering benefit, tables with frequent INSERT/UPDATE/DELETE, or when an existing index already supports the query.

Do more indexes automatically mean better performance?

No.

Which clauses should you look at when choosing what to index?

WHERE, JOIN, ORDER BY, and GROUP BY.

What four factors should you consider when choosing indexes?

Selectivity, query frequency, data modification frequency, and existing indexes.

What is selectivity?

How effectively a condition or column narrows down matching rows.

What is cardinality in indexing (Part 2)?

The number of distinct values in a column.

Does student_id have high or low selectivity?

High (many distinct values, few rows match each value).

Does year_level have high or low selectivity?

Low (few distinct values, many rows match each value).

What is the goal when creating indexes?

Provide meaningful performance benefits without unnecessary maintenance costs.

What is physical database optimization?

Optimization focused on how data and database structures are physically stored and accessed.

What is the main goal of physical database optimization?

Reduce unnecessary disk I/O and improve data access efficiency.

What are the six areas of physical database optimization?

Index organization, data storage, partitioning, memory/cache, fragmentation, and storage configuration.

What is a clustered index?

An index that organizes table data around the index key; closely tied to table data; typically one per table.

What is a non-clustered index?

A separate index structure containing keys and references to table rows; multiple can usually exist.

Clustered vs non-clustered in one phrase?

Clustered: data is organized around the index. Non-clustered: the index points to the data.

What are the three parts of a B-tree index?

Root, internal nodes, and leaf nodes.

What is the role of the root node in a B-tree?

It is the starting point of the search.

What is the role of internal nodes in a B-tree?

They guide the search.

What do leaf nodes in a B-tree hold?

The indexed keys or references to rows.

Why are B-trees popular?

They are balanced, search efficiently, support range queries, and reduce page access.

What is a heap table?

A table whose rows are stored without ordering by a particular index; separate indexes point to the rows.

What is an index-organized table?

A table whose data is stored within an index structure; the primary key determines the organization.

What causes fragmentation in a database?

INSERT, UPDATE, and DELETE operations changing pages.

What are the possible effects of fragmentation?

Less efficient storage, more pages to read, increased storage use, and possible performance degradation.

What maintenance actions address fragmentation?

Rebuild indexes, reorganize indexes, update statistics, and remove unnecessary indexes.

What is partitioning?

Dividing a large table or index into smaller sections called partitions.

What are the benefits of partitioning?

Easier management of large datasets, less scanned data through partition pruning, and simpler maintenance operations.