Changes between Version 2 and Version 3 of OtherDevelopments

Timestamp:

06/02/26 09:45:58 (2 weeks ago)

Author:

221296

Comment:

--

OtherDevelopments

@@ -1 @@
-== SQL Performance and Index Optimization ==
-
-The goal of this phase is to analyze and improve the performance of the most important database operations in the Online Learning Platform Management System.
-
-The application contains several frequent operations:
-
-browsing and filtering courses
-checking existing enrollments
-validating active user subscriptions
-loading course modules and lessons
-managing support tickets
-generating reports for enrollments, certificates and quiz attempts
-
-The following indexes are not automatically created by primary key constraints, but they can improve the performance of WHERE filters, JOIN operations and report queries.
-
-== Testing Methodology ==
-
-The performance analysis is done using PostgreSQL EXPLAIN ANALYZE.
-
-The testing process is:
-
-first, the query is executed without additional indexes
-then, the required index is created
-the same query is executed again
-the execution plan and execution time are compared
-
-This allows us to check whether PostgreSQL uses Seq Scan, Index Scan or another optimized query plan.
-
-== Scenario 1: Course Browsing and Filtering ==
-
-Users frequently browse available courses and filter them by course status, name or price.
-
-SQL query:
-
-{{{
-EXPLAIN ANALYZE
-SELECT c.course_id, c.name, c.price, c.status
-FROM course c
-WHERE c.status = 'ACTIVE'
-ORDER BY c.price;
-}}}
+== SQL Performance Analysis of Complex Queries ==
+
+The performance analysis in this phase focuses on the two most complex analytical queries developed in Phase 6.
+
+Instead of analyzing simple CRUD operations, we analyzed the most computationally expensive reports in the system. The goal was to identify the most expensive JOIN operations, grouping operations and date-based aggregations, and then improve their execution using indexes.
+
+For each query, we analyzed:
+
+JOIN conditions
+GROUP BY operations
+aggregate functions
+date-based filtering and grouping
+foreign key relationships
+
+The queries were tested using PostgreSQL EXPLAIN ANALYZE before and after index creation.
+
+=== Scenario 1: Yearly Analysis of Overall Platform Activity and Student Success ===
+
+This query provides a yearly analytical overview of the platform.
+
+It combines information from:
+
+courses
+enrollments
+certificates
+modules
+lessons
+quizzes
+quiz attempts
+
+The report calculates:
+
+total instructor activity
+total students
+total enrollments
+total quiz attempts
+completed enrollments
+total certificates
+average student success within courses
+average final grade per course
+final yearly success indicator
+
+The query performs multiple JOIN operations across the learning hierarchy:
+
+{{{
+Course → Module → Lesson → Quiz → QuizAttempt
+Enrollment → Certificate
+Enrollment → QuizAttempt
+}}}
+
+The most expensive operations are the joins between:
+
+{{{
+cert.enrollment_id = e.enrollment_id
+m.course_id = c.course_id
+l.module_id = m.module_id
+q.lesson_id = l.lesson_id
+qa.quiz_id = q.quiz_id
+qa.user_id = e.user_id
+}}}
+
+To improve performance, indexes were added on the columns used in the JOIN conditions.
 
 Indexes:
 
 {{{
-CREATE INDEX IF NOT EXISTS idx_course_status_price
-ON course(status, price);
-
-CREATE INDEX IF NOT EXISTS idx_course_name
-ON course(name);
-}}}
-
-Explanation:
-
-The composite index on (status, price) is useful because the query filters by status and sorts by price.
-
-The index on name improves course searching by course name.
-
-== Scenario 2: Enrollment Checking ==
-
-Before a user enrolls in a course, the system checks whether the user is already enrolled in that course.
-
-SQL query:
-
-{{{
-EXPLAIN ANALYZE
-SELECT e.enrollment_id, e.user_id, e.course_id, e.completion_status
-FROM enrollment e
-WHERE e.user_id = 1
-AND e.course_id = 3;
-}}}
-
-Index:
-
-{{{
-CREATE INDEX IF NOT EXISTS idx_enrollment_user_course
-ON enrollment(user_id, course_id);
-}}}
-
-Explanation:
-
-This index is useful because the application frequently searches enrollments by both user_id and course_id.
-
-Without this index, PostgreSQL may scan the whole enrollment table. With the index, the lookup is faster and more direct.
-
-== Scenario 3: Active Subscription Validation ==
-
-When a user wants to access course content, the system checks whether the user has an active subscription.
-
-SQL query:
-
-{{{
-EXPLAIN ANALYZE
-SELECT us.subscription_id, us.user_id, us.plan_id, us.status, us.end_date
-FROM user_subscription us
-WHERE us.user_id = 1
-AND us.status = 'ACTIVE'
-AND us.end_date >= CURRENT_DATE;
-}}}
-
-Index:
-
-{{{
-CREATE INDEX IF NOT EXISTS idx_user_subscription_active
-ON user_subscription(user_id, status, end_date);
-}}}
-
-Explanation:
-
-The index improves subscription validation because the query filters by user_id, status and end_date.
-
-This is important because subscription validation is performed before allowing access to paid course content.
-
-== Scenario 4: Instructor Course Management ==
-
-Instructors frequently manage their own courses. The system displays all courses created by a selected instructor.
-
-SQL query:
-
-{{{
-EXPLAIN ANALYZE
-SELECT c.course_id, c.name, c.price, c.status
-FROM course c
-WHERE c.instructor_id = 5;
-}}}
-
-Index:
-
-{{{
-CREATE INDEX IF NOT EXISTS idx_course_instructor_id
-ON course(instructor_id);
-}}}
-
-Explanation:
-
-The instructor_id column is used for filtering courses by instructor. This index improves the performance of the instructor dashboard.
-
-== Scenario 5: Course Content Loading ==
-
-When a user opens a course, the application loads all modules and lessons for that course.
-
-SQL query:
-
-{{{
-EXPLAIN ANALYZE
-SELECT
-m.module_id,
-m.title AS module_title,
-l.lesson_id,
-l.title AS lesson_title
-FROM module m
-JOIN lesson l ON l.module_id = m.module_id
-WHERE m.course_id = 3
-ORDER BY m.module_id, l.lesson_id;
-}}}
-
-Indexes:
-
-{{{
-CREATE INDEX IF NOT EXISTS idx_module_course_id
+CREATE INDEX IF NOT EXISTS idx_certificate_enrollment
+ON certificate(enrollment_id);
+
+CREATE INDEX IF NOT EXISTS idx_module_course
 ON module(course_id);
 
-CREATE INDEX IF NOT EXISTS idx_lesson_module_id
+CREATE INDEX IF NOT EXISTS idx_lesson_module
 ON lesson(module_id);
-}}}
-
-Explanation:
-
-The index on module(course_id) improves loading modules for a selected course.
-
-The index on lesson(module_id) improves loading lessons for each module.
-
-== Scenario 6: Admin Support Ticket Filtering ==
-
-Administrators frequently filter support tickets by status and check the newest tickets first.
-
-SQL query:
-
-{{{
-EXPLAIN ANALYZE
-SELECT st.ticket_id, st.subject, st.status, st.created_at
-FROM support_ticket st
-WHERE st.status = 'OPEN'
-ORDER BY st.created_at DESC;
-}}}
-
-Index:
-
-{{{
-CREATE INDEX IF NOT EXISTS idx_support_ticket_status_created
-ON support_ticket(status, created_at DESC);
-}}}
-
-Explanation:
-
-This index is useful because the admin panel filters tickets by status and sorts them by created_at.
-
-== Scenario 7: Annual Enrollment and Certificate Report ==
-
-This report analyzes enrollments, completed courses and issued certificates per year.
-
-SQL query:
-
-{{{
-EXPLAIN ANALYZE
-SELECT
-EXTRACT(YEAR FROM e.enroll_date) AS year,
-COUNT(DISTINCT e.enrollment_id) AS total_enrollments,
-COUNT(DISTINCT CASE
-WHEN e.completion_status = 'COMPLETED' THEN e.enrollment_id
-END) AS completed_enrollments,
-COUNT(DISTINCT c.certificate_id) AS issued_certificates,
-AVG(e.progress_percentage) AS average_progress
-FROM enrollment e
-LEFT JOIN certificate c ON c.enrollment_id = e.enrollment_id
-GROUP BY EXTRACT(YEAR FROM e.enroll_date)
-ORDER BY year;
-}}}
-
-Indexes:
-
-{{{
-CREATE INDEX IF NOT EXISTS idx_enrollment_enroll_date
+
+CREATE INDEX IF NOT EXISTS idx_quiz_lesson
+ON quiz(lesson_id);
+
+CREATE INDEX IF NOT EXISTS idx_quiz_attempt_quiz_user
+ON quiz_attempt(quiz_id, user_id);
+
+CREATE INDEX IF NOT EXISTS idx_enrollment_date
 ON enrollment(enroll_date);
-
-CREATE INDEX IF NOT EXISTS idx_enrollment_completion_status
-ON enrollment(completion_status);
-
-CREATE INDEX IF NOT EXISTS idx_certificate_enrollment_id
-ON certificate(enrollment_id);
-}}}
-
-Explanation:
-
-The index on enroll_date improves time-based reports.
-
-The index on completion_status helps when filtering completed enrollments.
-
-The index on certificate(enrollment_id) improves the join between certificate and enrollment.
-
-== Scenario 8: Quiz Attempts Report ==
-
-The system can analyze quiz attempts by user, quiz and attempt date.
-
-SQL query:
-
-{{{
-EXPLAIN ANALYZE
-SELECT
-qa.user_id,
-qa.quiz_id,
-COUNT(*) AS total_attempts,
-AVG(qa.score) AS average_score
-FROM quiz_attempt qa
-GROUP BY qa.user_id, qa.quiz_id
-ORDER BY qa.user_id;
-}}}
-
-Indexes:
-
-{{{
-CREATE INDEX IF NOT EXISTS idx_quiz_attempt_user_id
-ON quiz_attempt(user_id);
-
-CREATE INDEX IF NOT EXISTS idx_quiz_attempt_quiz_id
+}}}
+
+Explanation:
+
+The first five indexes reduce the cost of JOIN operations by allowing PostgreSQL to directly locate related records instead of scanning entire tables.
+
+The index on enroll_date improves the yearly grouping operation because the report groups data by year.
+
+=== Scenario 2: Monthly Analysis of Course Activity, Completions, Instructor Involvement and Student Success ===
+
+This query provides a month-by-month analytical overview of the platform for a selected year.
+
+The report combines:
+
+enrollment activity
+course completions
+instructor participation
+quiz performance
+student success metrics
+
+The query performs multiple JOIN operations through the complete learning structure:
+
+{{{
+QuizAttempt → Quiz → Lesson → Module → Course
+Enrollment → Course
+Enrollment → User
+}}}
+
+The most important JOIN conditions are:
+
+{{{
+qa.quiz_id = q.quiz_id
+q.lesson_id = l.lesson_id
+l.module_id = m.module_id
+m.course_id = c.course_id
+e.course_id = c.course_id
+}}}
+
+Additionally, the report groups data by:
+
+{{{
+YEAR(enroll_date)
+MONTH(enroll_date)
+
+YEAR(attempt_date)
+MONTH(attempt_date)
+}}}
+
+To optimize these operations, the following indexes were added:
+
+{{{
+CREATE INDEX IF NOT EXISTS idx_module_course
+ON module(course_id);
+
+CREATE INDEX IF NOT EXISTS idx_lesson_module
+ON lesson(module_id);
+
+CREATE INDEX IF NOT EXISTS idx_quiz_lesson
+ON quiz(lesson_id);
+
+CREATE INDEX IF NOT EXISTS idx_quiz_attempt_quiz
 ON quiz_attempt(quiz_id);
+
+CREATE INDEX IF NOT EXISTS idx_enrollment_course
+ON enrollment(course_id);
+
+CREATE INDEX IF NOT EXISTS idx_enrollment_date
+ON enrollment(enroll_date);
 
 CREATE INDEX IF NOT EXISTS idx_quiz_attempt_date
@@ -272 +157 @@
 Explanation:
 
-These indexes improve reports that analyze quiz attempts by user, quiz or date.
+Indexes on foreign key columns improve JOIN performance, while indexes on date columns improve monthly grouping and chronological analysis.
+
+These indexes reduce the amount of data PostgreSQL needs to scan when generating monthly reports.
 
 = Security and Protection Measures =