Lab 8 — Query Performance and Database Automation

IT079 · Principles of Database Management · EduConnect Lab Series

Part 1 — Lab Overview

Field Details

Course IT079 — Principles of Database Management

Lab Number Lab 8 of 10

Target Year 2, Semester 4

Students

Duration 2.5 hours (in-lab) + 1 hour self-study

Prerequisites Labs 3–5 completed — EduConnect schema and queries working

Tools MySQL 8.x + MySQL Workbench

CO Coverage CO3: Construct efficient SQL queries; understand database performance

and automation

Project Context
You can now write correct SQL queries. But “correct” and “fast” are different things. A
query that returns the right answer in 0.02 seconds on your laptop may take 45 seconds on a
production server with 500,000 rows. This lab introduces the tools that bridge that gap:
indexes to speed up queries, EXPLAIN to diagnose slow queries, views to simplify complex
queries, and stored procedures as an introduction to database-side automation.
The emphasis is on understanding why — why an index makes a query faster, why a full
table scan is slow, why views exist, and when database automation is appropriate versus
when it creates more problems than it solves.

What You Will Build

• An analysis of three slow queries using EXPLAIN before and after indexing
• Two views used in EduConnect reporting
• One stored procedure with input validation
• A written performance report comparing query execution with and without indexes

Part 2 — Learning Objectives

By the end of this lab, students will be able to:

1
 1. Run EXPLAIN on a query and interpret the key columns: type, key, rows, Extra
2. Identify full table scans and explain why they are slow at scale
3. Design single-column and composite indexes for common query patterns
4. Measure the performance impact of an index using rows examined before and after
5. Create views and query them like tables
6. Distinguish updatable views from non-updatable views
7. Write a basic stored procedure with IN parameters and input validation
8. Explain why stored procedures are used less in modern systems and when they remain
appropriate

Part 3 — Theory & Concepts

3.1 Why Queries Become Slow

When MySQL executes a query, it needs to find the rows that match your conditions.
Without any help, it does this by reading every single row in the table and checking each one
— a full table scan. On a 10-row table this is instant. On a 500,000-row table this takes
seconds.

EduConnect with 500,000 enrollments:

SELECT * FROM Enrollments WHERE UserID = 42;

Without index: MySQL reads all 500,000 rows, checks each one.
With index: MySQL jumps directly to the ~5 rows for UserID 42.

Time difference: potentially 100x faster.

The query is the same. The data is the same. The only difference is the index.

3.2 What Is an Index?

An index is a separate data structure that MySQL maintains alongside a table. The default
index type in MySQL is a B-tree (Balanced Tree) — a structure that keeps values sorted and
allows fast lookup by jumping directly to the right position.
Think of it like a textbook index: instead of reading every page to find “foreign key”, you go
to the index, find the page numbers, and jump there directly.
What indexes cost:
Indexes are not free. Every INSERT, UPDATE, or DELETE must also update all indexes on
that table.

Operation No index With index

SELECT (filtered) Slow on large tables Fast

2
Operation No index With index

INSERT Fast Slightly slower

UPDATE Fast Slightly slower

DELETE Fast Slightly slower

Disk storage Less More

Rule of thumb: Add indexes for columns you frequently filter, join, or sort by. Do not
index every column. A table with 15 indexes on a write-heavy system can be slower than the
same table with 3 well-chosen indexes.

3.3 Reading EXPLAIN

EXPLAIN shows MySQL’s execution plan without actually running the query.

EXPLAIN SELECT [Link]

FROM Users u
JOIN Enrollments e ON [Link] = [Link]
WHERE [Link] = 'student';

Key columns:

Column What it means What to look for

type How MySQL accesses ALL = full scan (problem). ref = index used (good).
rows eq_ref = unique index (best).

key Which index was used NULL = no index — full scan in progress
rows Estimated rows MySQL Large number with type = ALL = performance problem
will examine
Extra Additional information Using filesort = extra sort step needed. Using index
= data from index only, very fast.
The most important column is type. A value of ALL on a large table is the primary signal
that an index is needed.

3.4 Composite Indexes — Column Order Matters

A composite index covers multiple columns. The column order determines which queries it
can help.

CREATE INDEX idx_enroll_user_course ON Enrollments(UserID, CourseID);

This index helps: - WHERE UserID = ? — uses the leftmost column - WHERE UserID = ? AND
CourseID = ? — uses both columns

3
This index does NOT help: - WHERE CourseID = ? alone — cannot start from the middle of
the index
Leftmost prefix rule: A composite index on (A, B, C) helps queries filtering on A, A+B, or
A+B+C — but not B alone or C alone. Always put the most frequently filtered column first.

3.5 Views — Named Queries

A view is a stored SELECT query referenced like a table. It does not store data — MySQL
reruns the underlying SELECT fresh every time you query the view.

CREATE VIEW StudentEnrollmentSummary AS

SELECT
[Link],
[Link],
COUNT([Link]) AS TotalEnrolled,
ROUND(AVG([Link]), 1) AS AverageGrade
FROM Users u
LEFT JOIN Enrollments e ON [Link] = [Link]
WHERE [Link] = 'student'
GROUP BY [Link], [Link];

After creating this view, query it like any table:

SELECT * FROM StudentEnrollmentSummary WHERE AverageGrade > 7.0;

When views are useful:

- A complex multi-join query is used repeatedly by multiple callers
- Restricting which columns a reporting tool or user can see
- Providing a stable interface when the underlying table structure may change
When views are not useful: - As a performance optimisation — MySQL views are not
cached. A slow query in a view is still slow. - For simple queries faster to write inline
Updatable vs non-updatable: A view is updatable (INSERT/UPDATE through it) only if it
references exactly one base table with no GROUP BY, DISTINCT, aggregate functions, or
subqueries in SELECT. StudentEnrollmentSummary above is not updatable because of
GROUP BY and COUNT.

3.6 Stored Procedures — Introduction

A stored procedure is a named block of SQL logic stored in the database and called by name.

DELIMITER //
CREATE PROCEDURE GetStudentReport(IN p_UserID INT)
BEGIN
SELECT
[Link],
[Link],
IFNULL(CAST([Link] AS CHAR), 'Pending') AS Grade

4
 FROM Enrollments e
JOIN Courses c ON [Link] = [Link]
WHERE [Link] = p_UserID
ORDER BY [Link];
END //
DELIMITER ;

CALL GetStudentReport(1);

Why DELIMITER is needed: MySQL uses ; to end statements. Inside a procedure there
are multiple ;-terminated statements. Changing the delimiter to // tells MySQL to treat the
entire block as one unit.
When stored procedures are still used in practice: - Legacy enterprise systems — banks,
ERPs, large corporations with existing SQL Server or Oracle infrastructure - Reducing
network round-trips for multi-step operations - Centralising logic shared by multiple
applications (web, mobile, admin tool) that cannot share application code
Why modern teams often avoid them: - Hard to version control alongside application code
— the procedure lives in the DB server, not in Git - Hard to unit test in isolation - Logic
hidden in the database is invisible to developers reading the application - ORMs (Hibernate,
Laravel Eloquent, Django ORM) make them unnecessary for most CRUD operations
A note on triggers: Triggers are SQL that run automatically on
INSERT/UPDATE/DELETE. They have legitimate uses — audit logging is the most widely
accepted. However, they create invisible side effects that make debugging difficult. Most
experienced teams have a policy of avoiding triggers in application logic. You will encounter
them in professional environments, so knowing they exist and how they work conceptually is
important.

3.7 Why This Matters in Real Systems

The single most common cause of “the application is slow” in production is missing
indexes. Not bad algorithms, not server hardware — a table that grew from 1,000 rows to
1,000,000 rows over 18 months while the query was doing a full scan every time. The query
worked fine at launch. Nobody noticed until users started complaining.
Understanding EXPLAIN and index design is what separates a developer who can build a
system from one who can keep it running under load.

Part 4 — Hands-On Guide

4.1 Verify Your Database

USE educonnect;
SELECT 'Users' AS tbl, COUNT(*) AS n FROM Users
UNION ALL SELECT 'Courses', COUNT(*) FROM Courses
UNION ALL SELECT 'Enrollments', COUNT(*) FROM Enrollments

5
 UNION ALL SELECT 'Lessons', COUNT(*) FROM Lessons
UNION ALL SELECT 'Attendance', COUNT(*) FROM Attendance;

Expected: 8, 5, 11, 5, 9. If different, re-run the seed data from Lab 4.

4.2 Worked Example: EXPLAIN Before and After Index

Step 1 — Check existing indexes and run baseline EXPLAIN:

SHOW INDEX FROM Enrollments;

EXPLAIN
SELECT [Link], [Link]
FROM Users u
JOIN Enrollments e ON [Link] = [Link]
WHERE [Link] = 'student'
AND [Link] IS NOT NULL
ORDER BY [Link] DESC;

Record type and rows for both tables.

Step 2 — Add an index and re-run EXPLAIN:

CREATE INDEX idx_enroll_userid ON Enrollments(UserID);

EXPLAIN
SELECT [Link], [Link]
FROM Users u
JOIN Enrollments e ON [Link] = [Link]
WHERE [Link] = 'student'
AND [Link] IS NOT NULL
ORDER BY [Link] DESC;

The type for Enrollments should change from ALL to ref. The key column should show
idx_enroll_userid. The rows estimate should decrease.

Step 3 — Document in a comparison table:

Table type (before) key (before) rows (before) type (after) key (after) rows (after)

Users

Enrollments

4.3 Worked Example: Creating and Querying a View

CREATE VIEW CourseEnrollmentStatus AS
SELECT
[Link],
[Link] AS CourseTitle,
[Link] AS Department,
[Link] AS Instructor,
COUNT([Link]) AS EnrollmentCount,

6
 ROUND(AVG([Link]), 1) AS AverageGrade
FROM Courses c
JOIN Departments d ON [Link] = [Link]
JOIN Users u ON [Link] = [Link]
LEFT JOIN Enrollments e ON [Link] = [Link]
GROUP BY [Link], [Link], [Link], [Link];

-- Query the view

SELECT * FROM CourseEnrollmentStatus;

-- Filter: courses with no enrollments

SELECT * FROM CourseEnrollmentStatus WHERE EnrollmentCount = 0;

The second query answers “which courses have no enrollments?” in one readable line
because the complex aggregation is hidden inside the view.

Part 5 — Exercises

Exercise 1 — EXPLAIN and Index Design (Basic)

Estimated time: 40 minutes

Task 1.1 — Baseline EXPLAIN

Run EXPLAIN on each query before adding any new indexes. Record type, key, and rows for
every table in the plan.
Query A:

SELECT * FROM Attendance

WHERE UserID = 1 AND Status = 'absent';

Query B:

SELECT [Link], [Link]

FROM Courses c
JOIN Enrollments e ON [Link] = [Link]
WHERE [Link] IS NOT NULL
ORDER BY [Link] DESC;

Query C:

SELECT [Link], COUNT([Link]) AS AbsenceCount

FROM Users u
JOIN Attendance a ON [Link] = [Link]
WHERE [Link] = 'absent'
GROUP BY [Link], [Link]
HAVING COUNT([Link]) > 0;

Query Table type (before) key (before) rows (before)

A Attendance

7
Query Table type (before) key (before) rows (before)

B Courses

B Enrollments

C Users

C Attendance
What you must explain: > For each query where type = ALL, explain in plain language
what MySQL is doing. Why does this become a critical problem when the table has
1,000,000 rows?

Task 1.2 — Design and Apply Indexes

For each query above, design an appropriate index, create it, and re-run EXPLAIN.
Requirements: - Write a CREATE INDEX statement for each query that would benefit - For
Query A: consider whether a single-column or composite index is more appropriate - For
Query C: think carefully — Status has only 3 possible values. Is indexing it alone useful?
Constraints: Check SHOW INDEX FROM table before creating — do not duplicate existing
indexes.

Query Index created type (after) key (after) rows (after)

C
What you must explain: > Query C filters Status = 'absent'. A classmate says low-
cardinality columns should not be indexed because MySQL does a full scan anyway. Is this
always true? Under what conditions would an index on Status actually help? Under what
conditions would MySQL ignore it?

Task 1.3 — The Composite Index Decision

The EduConnect attendance system runs this query thousands of times per day:

SELECT Status, Note

FROM Attendance
WHERE UserID = ? AND LessonID = ?;

Two options are proposed:

• Option A: CREATE INDEX idx_att_user ON Attendance(UserID)
• Option B: CREATE INDEX idx_att_user_lesson ON Attendance(UserID, LessonID)

8
Tasks: 1. Create Option A. Run EXPLAIN. Record rows and Extra. 2. Drop Option A (DROP
INDEX idx_att_user ON Attendance ). Create Option B. Run EXPLAIN again. 3. Compare
the two results.
What you must explain: > Option B may be a covering index for this query. How does the
Extra column in EXPLAIN tell you this? What does Using index mean, and why is it faster
than Using where?

Exercise 2 — Views (Intermediate)

Estimated time: 35 minutes

Task 2.1 — Two Reporting Views

View 1: StudentProgressReport
For every student, show: - StudentName - TotalEnrolled — total courses enrolled -
TotalGraded — courses where Grade IS NOT NULL - TotalUngraded — courses where
Grade IS NULL - AverageGrade — average of graded enrollments, rounded to 1 decimal,
NULL if none graded - TotalAbsences — Attendance records where Status = ‘absent’
Students with no enrollments must appear with zero counts.
View 2: AtRiskStudents
Built on StudentProgressReport. Show students where AverageGrade < 6.0 OR
TotalAbsences > 1.
Test both views:

SELECT * FROM StudentProgressReport;

SELECT * FROM AtRiskStudents;

What you must explain: > AtRiskStudents is a view built on top of another view. When you
run SELECT * FROM AtRiskStudents , how many SELECT queries does MySQL actually
execute internally? Does this have a performance cost? When would you replace a view-on-
view with a flat CTE query instead?

Task 2.2 — Test View Updatability

Run these operations and document exactly what happens (success or error message):

-- Test 1: INSERT through non-updatable view

INSERT INTO StudentProgressReport (StudentName) VALUES ('Test');

-- Test 2: UPDATE through non-updatable view

UPDATE CourseEnrollmentStatus SET AverageGrade = 9.0
WHERE CourseTitle = 'Database Management';

-- Test 3: updatable view (create, test, restore)

CREATE VIEW StudentNamesOnly AS
SELECT UserID, FullName FROM Users WHERE Role = 'student';

9
 UPDATE StudentNamesOnly SET FullName = 'Nguyen Van Test' WHERE UserID = 1;
SELECT UserID, FullName FROM Users WHERE UserID = 1;
UPDATE StudentNamesOnly SET FullName = 'Nguyen Van An' WHERE UserID = 1;

What you must explain: > Test 3 succeeds but Tests 1 and 2 fail. List the specific properties
of StudentProgressReport that make it non-updatable. List the properties of
StudentNamesOnly that make it updatable. Why does this distinction matter in a real
application?

Exercise 3 — Stored Procedure and Critical Thinking (Advanced)

Estimated time: 35 minutes

Task 3.1 — Validated Stored Procedure

Write a procedure GetStudentFullRecord(IN p_UserID INT) that: - If UserID does not exist:
returns the message 'ERROR: User not found' - If UserID is an instructor: returns 'ERROR:
User is not a student' - If valid student: returns their enrollments (CourseTitle,
EnrollDate, Grade or ‘Pending’) ordered by EnrollDate

DELIMITER //
CREATE PROCEDURE GetStudentFullRecord(IN p_UserID INT)
BEGIN
DECLARE v_Role VARCHAR(20) DEFAULT NULL;

SELECT Role INTO v_Role FROM Users WHERE UserID = p_UserID;

IF v_Role IS NULL THEN

SELECT 'ERROR: User not found' AS Message;
ELSEIF v_Role != 'student' THEN
SELECT 'ERROR: User is not a student' AS Message;
ELSE
SELECT
[Link] AS CourseTitle,
[Link],
CASE WHEN [Link] IS NULL
THEN 'Pending'
ELSE CAST([Link] AS CHAR) END AS Grade
FROM Enrollments e
JOIN Courses c ON [Link] = [Link]
WHERE [Link] = p_UserID
ORDER BY [Link];
END IF;
END //
DELIMITER ;

Test cases:

10
 CALL GetStudentFullRecord(1); -- valid student
CALL GetStudentFullRecord(6); -- instructor
CALL GetStudentFullRecord(999); -- non-existent

What you must explain: > List one concrete advantage of having this logic in a stored
procedure. List one concrete disadvantage. Which would you choose for a new project and
why?

Task 3.2 — When Not to Use Stored Procedures

A junior developer proposes creating a stored procedure for every database operation on
EduConnect. Answer the following questions in writing. No code required.
1. The team uses Git. Stored procedures live in the MySQL server, not in any repository
file. What specific problem does this create during collaborative development?
2. The team wants automated tests for grade update logic. How does having the logic in
a stored procedure make testing harder compared to a Java method?
3. Six months later the team migrates from MySQL to PostgreSQL. What additional
work does the stored procedure approach create?
4. Despite these disadvantages, name one specific EduConnect operation where you
would still recommend a stored procedure. Justify your choice referencing at least one
of: performance, maintainability, security, or team structure.
What you must explain: > Your answer to question 4 must go beyond “it is faster.” Explain
the specific system context that makes a stored procedure the better choice in that scenario.

Part 6 — Common Mistakes

Mistake Why It Is Wrong What to Do Instead

Adding indexes Every index slows INSERT, UPDATE, Only index columns in
on every column DELETE. A write-heavy table with 15 frequent WHERE, JOIN ON,
indexes can be slower than one with 3 or ORDER BY clauses.
well-chosen indexes. Verify with EXPLAIN.

Indexing low- A column with 2–3 distinct values (Role, Use composite indexes that
cardinality Status) may not help if the matching pair low-cardinality with
columns alone rows are a large fraction of the table — high-cardinality columns.
MySQL skips the index and scans Test with EXPLAIN.
anyway.

Reading Students often focus only on rows. But Always check type first. ALL
EXPLAIN and rows = 1 with type = ALL is still a full is always worth investigating.
ignoring type scan — it just got lucky on small data.

Creating a view MySQL views are not materialised. Fix the slow query with

11
Mistake Why It Is Wrong What to Do Instead

to speed up a Querying a view reruns the underlying indexes first. Then wrap in a
slow query SELECT every time. view for readability.

Composite index Index on (CourseID, UserID) cannot Put the most frequently
with wrong help WHERE UserID = ? alone — leftmost filtered column first. Verify
column order prefix rule. with EXPLAIN.

Stored An unvalidated procedure will attempt Always validate inputs at the

procedure with operations on non-existent or wrong-type start: check existence, check
no input data, producing confusing errors or silent Role, check ranges.
validation wrong results.

Part 7 — Lab Completion Checklist

EXPLAIN AND INDEXES
[ ] Task 1.1: EXPLAIN run on all 3 queries before indexing; table completed
[ ] Task 1.1: Written explanation of full scan impact at scale
[ ] Task 1.2: Indexes created; EXPLAIN re-run; improvement table completed
[ ] Task 1.2: Low-cardinality index reasoning written
[ ] Task 1.3: Both Option A and B tested; rows and Extra compared
[ ] Task 1.3: Covering index and "Using index" explained in writing

VIEWS
[ ] Task 2.1: StudentProgressReport created; zero-enrollment students
appear
[ ] Task 2.1: AtRiskStudents created using StudentProgressReport; tested
[ ] Task 2.2: All 3 updatability tests run; results documented
[ ] Task 2.2: Updatable vs non-updatable properties explained

STORED PROCEDURES
[ ] Task 3.1: GetStudentFullRecord created; all 3 test cases pass
[ ] Task 3.1: Trade-off answered with concrete reasoning
[ ] Task 3.2: All 4 scenario questions answered in writing
[ ] Task 3.2: Question 4 justified with specific system context

GENERAL
[ ] All EXPLAIN results documented in table format
[ ] Every design decision includes written justification

Part 8 — Thinking Questions

Q1. The query planner and index selectivity You added an index on Enrollments(UserID).
But when you run SELECT * FROM Enrollments WHERE UserID > 0 , MySQL may still do a
full table scan even though the index exists. Why would the query planner ignore a valid
index? What property of the query makes the index unhelpful here?
Q2. Index maintenance overhead at scale The Enrollments table has three indexes: primary
key on EnrollID, unique key on (UserID, CourseID), and your new index from Task 1.2. At

12
end of semester, 10,000 students enroll simultaneously over two hours. How does the number
of indexes affect INSERT throughput? What MySQL metric would you monitor to detect this
becoming a bottleneck?
Q3. Views and ORMs Modern frameworks like Laravel, Django, and Spring Data JPA use
ORMs that query base tables directly — they almost never query views. Given this, what is
the practical value of creating views in an ORM-based project? Name one realistic
EduConnect scenario where you would create a view even though the application uses an
ORM.
Q4. Stored procedure versioning in practice A stored procedure has a bug that caused
incorrect grades to be recorded for two weeks. The team wants to know exactly when the bug
was introduced and what the procedure looked like before. How would they determine this if
procedures are stored only in the MySQL server? What operational practice would have
made this investigation straightforward?
Q5. The slow query log MySQL’s slow query log records every query that exceeds a
configurable threshold. A query that was fast for two years suddenly appears in the log. What
are the three most likely causes? For each, what is the first thing you would check?
Q6. EXPLAIN vs EXPLAIN ANALYZE MySQL 8.0.18 introduced EXPLAIN ANALYZE,
which runs the query and compares estimated row counts with actual row counts. When
would the estimated and actual counts differ significantly? What does a large discrepancy tell
you? How do you fix it?

End of Lab 7 · EduConnect Lab Series · IT079 Principles of Database Management

International University — Vietnam National University HCMC

13