Skip to main content

SQL Best Practices and Optimization

Last stop! You’ve built the University schema, queried data, and managed security. This lesson wraps things up with habits that keep your SQL maintainable and fast as the dataset grows.

Learning Goals

  • Apply formatting and naming conventions to University queries
  • Tune statements using indexes and execution plans
  • Avoid common pitfalls that surface at scale
  • Balance normalization with performance needs

Write Readable Queries​

Consistent formatting and descriptive aliases make joins easier to understand.

Formatted join across core tables
SELECT
  s.student_id,
  s.first_name || ' ' || s.last_name AS student_name,
  e.semester,
  c.course_code,
  c.course_title,
  d.dept_name
FROM student    AS s
JOIN enrollment AS e ON e.student_id = s.student_id
JOIN course     AS c ON c.course_id  = e.course_id
JOIN department AS d ON d.dept_id    = c.dept_id
WHERE e.semester = 'Spring 2025'
ORDER BY student_name, c.course_code;

Tips:

  • Use uppercase for SQL keywords and lowercase for identifiers or follow your team’s standard.
  • Avoid SELECT * in production; list the columns you need.
  • Add comments when business rules aren’t obvious.

Efficient Filtering​

Conditions that preserve index usage (“sargable” filters) keep queries fast.

SELECT course_code, course_title
FROM course
WHERE dept_id = 1
  AND credits >= 3;

Avoid wrapping indexed columns in functions: WHERE LOWER(course_code) = 'cs101' prevents index usage. Instead normalize the data at insert time or compare case-insensitively with database-specific operators.

Index Strategy Recap​

  • Index foreign keys: student_id and course_id in enrollment.
  • Consider composite indexes for frequent filters ((student_id, semester)).
  • Review indexes regularly—too many slow down writes.
  • Run EXPLAIN (SQLite: EXPLAIN QUERY PLAN) to confirm your query uses the expected index.
EXPLAIN QUERY PLAN
SELECT *
FROM enrollment
WHERE student_id = 3 AND semester = 'Fall 2025';

Manage Data Growth​

Keep Transactions Short​

Long transactions block others. Batch grade updates carefully:

BEGIN TRANSACTION;
UPDATE enrollment
SET grade = 'A'
WHERE course_id = 7
  AND semester = 'Spring 2025'
  AND student_id IN (3, 4, 5);
COMMIT;

Archive Historical Rows​

If your system accumulates years of enrollment data, consider partitioning or archiving old semesters into separate tables or schemas. An archive.enrollment_2020 table keeps production tables lean.

Common Pitfalls​

  • Forgetting WHERE in updates: Always test with SELECT first:

    SELECT * FROM enrollment WHERE semester = 'Fall 2025';

    Then update with the same filter.

  • N+1 query patterns: Fetch related data with joins instead of looping through students and running separate queries per student.

  • Hard-coded literals: Use parameters or configuration tables (term_settings) so you can switch semesters without editing SQL.

  • Ignoring execution plans: A slow query deserves an EXPLAIN to see whether it scans the whole table.

  • Skipping normalization: Keep the schema normalized for consistency. Denormalize only when profiling shows joins are the bottleneck and the data changes infrequently.

Performance Checklist​

  1. Clarify goal – Which question are you answering? Only fetch required data.
  2. Profile first – Measure baseline performance; avoid premature optimization.
  3. Add indexes – Based on real query patterns, not speculation.
  4. Simplify – Break complex logic into CTEs or views, but ensure they aren’t chained inefficiently.
  5. Monitor – Keep an eye on slow-query logs and staging environments before pushing to production.

Summary​

  • Clean formatting, explicit columns, and clear aliases make your University queries easier to maintain.
  • Index high-value columns and regularly review query plans.
  • Keep transactions short, archive old data, and avoid N+1 patterns.
  • Optimize only after measuring—let real usage guide your tuning.

Quiz​

Show quiz

  1. Why should you avoid SELECT * when querying enrollment?
    A) It fails in SQLite
    B) It may return unnecessary columns and hide schema changes
    C) It blocks index usage
    D) It prevents joins

  2. Which tool reveals whether a query performs a full table scan?
    A) DESCRIBE
    B) EXPLAIN/EXPLAIN QUERY PLAN
    C) VACUUM
    D) ANALYZE TABLE

  3. What should you do before running UPDATE enrollment SET grade = 'A';?
    A) Nothing; it is safe
    B) Run a SELECT with the same WHERE clause you plan to use
    C) Drop existing indexes
    D) Change isolation level to SERIALIZABLE

  4. True or False: Adding indexes always improves performance.
    A) True
    B) False

Answers

  1. B
  2. B
  3. B
  4. B