Changes between Initial Version and Version 1 of OtherDevelopments

Timestamp:

05/28/26 09:29:22 (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;
+}}}
+
+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
+ON module(course_id);
+
+CREATE INDEX IF NOT EXISTS idx_lesson_module_id
+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
+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
+ON quiz_attempt(quiz_id);
+
+CREATE INDEX IF NOT EXISTS idx_quiz_attempt_date
+ON quiz_attempt(attempt_date);
+}}}
+
+Explanation:
+
+These indexes improve reports that analyze quiz attempts by user, quiz or date.