CREATE INDEX & AUTO INCREMENT

An index is like a book index — the database finds rows faster without scanning every page. Too many indexes slow down writes.

CREATE INDEX — speed up lookups

An index is like a book index — the database finds rows faster without scanning every page. Too many indexes slow down writes.

Real-life example: CREATE INDEX on student_id is alphabet tabs on the attendance drawer — jump straight to 'P' for Patel.

Indexes on common filter columns

CREATE INDEX idx_students_city ON students(city);
CREATE INDEX idx_enrollments_student ON enrollments(student_id);
CREATE INDEX idx_enrollments_course_score ON enrollments(course_id, score);

AUTO INCREMENT — automatic IDs

AUTO_INCREMENT (MySQL), SERIAL (PostgreSQL), and IDENTITY (SQL Server) generate the next integer ID automatically.

Real-life example: AUTO INCREMENT is the ticket machine at the deli — each new customer gets the next number without you choosing.

Auto-generated primary keys

-- MySQL
CREATE TABLE students (
  student_id INT AUTO_INCREMENT PRIMARY KEY,
  full_name VARCHAR(100) NOT NULL
);

INSERT INTO students (full_name) VALUES ('New Student');

-- PostgreSQL
CREATE TABLE students (
  student_id SERIAL PRIMARY KEY,
  full_name VARCHAR(100) NOT NULL
);