Skip to main content

SQL Best Practices and Optimization

Last stop. You've built the marketplace 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 marketplace 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
  c.customer_id,
  CONCAT(c.first_name, ' ', c.last_name) AS customer_name,
  o.order_date,
  p.sku,
  p.product_name,
  cat.category_name
FROM orders     AS o
JOIN customer   AS c   ON c.customer_id = o.customer_id
JOIN order_item AS oi  ON oi.order_id = o.order_id
JOIN product    AS p   ON p.product_id = oi.product_id
JOIN category   AS cat ON cat.category_id = p.category_id
WHERE o.status = 'Delivered'
ORDER BY customer_name, o.order_date;

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 sku, product_name
FROM product
WHERE category_id = 1
  AND price >= 30;

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

Index Strategy Recap​

  • Index foreign keys: order_item.order_id, order_item.product_id, orders.customer_id.
  • Consider composite indexes for frequent filters ((customer_id, order_date)).
  • Review indexes regularly - too many slow down writes.
  • Run EXPLAIN to confirm your query uses the expected index.
EXPLAIN
SELECT *
FROM orders
WHERE customer_id = 3 AND order_date LIKE '2025-03%';

Manage Data Growth​

Keep Transactions Short​

Long transactions block others. Batch status updates carefully:

START TRANSACTION;
UPDATE orders
SET status = 'Delivered',
    delivered_at = '2025-03-31'
WHERE order_id IN (3, 4, 7);
COMMIT;

Archive Historical Rows​

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

Common Pitfalls​

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

    SELECT * FROM orders WHERE status = 'Pending';

    Then update with the same filter.

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

  • Hard-coded literals: Use parameters or configuration tables (reporting_periods) so you can switch dates 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 marketplace 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 order_item?
    A) It is required to use SELECT * with joins
    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
    C) VACUUM
    D) ANALYZE TABLE

  3. What should you do before running UPDATE orders SET status = 'Delivered';?
    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