lesson1Title

lesson2Title

lesson3Title

lesson4Title

lesson5Title

lesson6Title

lesson7Title

lesson8Title

lesson9Title

lesson10Title

lesson11Title

lesson12Title

sqlIntroAdvancedChapter3Title

sqlIntroAdvancedChapter1Title

sqlIntroAdvancedChapter2Title

-- Create the students table
CREATE TABLE students (
  id INTEGER PRIMARY KEY,
  name TEXT,
  grade INTEGER
);

-- Insert sample data
INSERT INTO students (name, grade) VALUES
  ('Alex', 92),
  ('Sara', 85),
  ('Daniel', 90),
  ('Mia', 78),
  ('John', 95);

-- 1. Create an index for faster lookups by name
CREATE INDEX idx_student_name ON students(name);

-- 2. Avoid SELECT *: fetch only needed columns
SELECT name, grade FROM students;

-- 3. Use EXPLAIN QUERY PLAN to analyze performance
EXPLAIN QUERY PLAN
SELECT name FROM students WHERE grade > 90;

-- 4. Example of filtering before joining
SELECT *
FROM (SELECT * FROM orders WHERE status = 'paid') AS o
JOIN customers c ON o.customer_id = c.id;

-- 5. Use LIMIT during development
SELECT * FROM orders LIMIT 100;

# Performance Tips

Efficient SQL matters, especially as your database grows. 

In this lesson, you’ll learn how to optimize queries using indexes, execution plans, and smart design strategies.

 

## 1. Use Indexes

An *index* is like a table of contents for your database.

They help the system locate rows faster, especially in large tables.

```sql title="Create an index"
CREATE INDEX idx_student_name ON students(name);
```

### When to use indexes:

You can use indexes in the following cases:

* Columns used in `WHERE`, `JOIN`, or `ORDER BY`
* Tables with lots of rows and frequent lookups

> Avoid excessive indexing. Indexes speed up reads but slow down writes.

 

## 2. Avoid SELECT \*

Selecting only the required columns reduces I/O and memory usage.

```sql title="SELECT * vs SELECT name, grade"
-- Not recommended
SELECT * FROM students;

-- Better
SELECT name, grade FROM students;
```

 

## 3. Use EXPLAIN or EXPLAIN QUERY PLAN

Use `EXPLAIN` to understand how a query runs behind the scenes.

```sql title="Use EXPLAIN"
EXPLAIN QUERY PLAN
SELECT name FROM students WHERE grade > 90;
```

This helps you identify:

* Full table scans (slow)
* Use of indexes (fast)
* Inefficient joins or sorts

 

## 4. Filter Early, Join Later

Reduce rows **before** joining to improve query performance.

```sql title="Early filtering example"
-- Better approach
SELECT *
FROM (SELECT * FROM orders WHERE status = 'paid') AS o
JOIN customers c ON o.customer_id = c.id;
```

 

## 5. Use LIMIT During Development

To avoid expensive mistakes when testing queries:

```sql title="Limit 100 rows"
SELECT * FROM logs LIMIT 100;
```

 

## Summary

| Strategy | Benefit |
| ------------------ | ---------------------------- |
| Indexes | Faster searches and joins | 
| Avoid `SELECT *` | Reduces unnecessary data scans |
| Use `EXPLAIN` | Reveal performance issues |
| Filter before join | Smaller, faster joins |
| Use `LIMIT` in dev | Prevent long-running queries |

Indexes help the database quickly locate rows. They are most effective on columns that appear in `WHERE` filters, `JOIN` conditions, or `ORDER BY` clauses. However, too many indexes can slow down write operations like INSERT or UPDATE.

### When should you create an index in SQL to improve query performance?

Strategy	Benefit
Indexes	Faster searches and joins
Avoid `SELECT *`	Reduces unnecessary data scans
Use `EXPLAIN`	Reveal performance issues
Filter before join	Smaller, faster joins
Use `LIMIT` in dev	Prevent long-running queries