SQL · Theory

Create Index

← All stacks

Theory

77/439

Create Index

Asha only reaches for Create Index when lookup by roll has to stay correct in a big table.

Keep Create Index small. Asha should finish lookup by roll in a few lines, not a 40-line dump.

Skip Create Index and indexing a low-cardinality flag only shows up in a big table.

Create Index shows up in a big table. Name lookup by roll, not “a real-world scenario”.

Create Index miss — indexing a low-cardinality flag only. Fix it before you talk about advanced SQL.

After Create Index, Asha should still remember indexing a low-cardinality flag only.

One breath for Create Index, then lookup by roll, then indexing a low-cardinality flag only. Sit down.

Exam tip

For Create Index: definition + a big table + one failure.

Create Index — sample query

CREATE TABLE authors (
  id INTEGER PRIMARY KEY,
  email TEXT UNIQUE NOT NULL
);
CREATE TABLE books (
  id INTEGER PRIMARY KEY,
  author_id INTEGER NOT NULL,
  title TEXT NOT NULL,
  FOREIGN KEY (author_id) REFERENCES authors(id)
);
INSERT INTO authors VALUES (1, 'a@x.com');
INSERT INTO books VALUES (1, 1, 'SQL Basics');
SELECT * FROM books;

Create Index — keys and constraints protect data integrity; indexes speed lookups.

Short notes

DefCreate Index — Asha uses it for lookup by roll in a big table.
RuleCreate Index → PRIMARY KEY(roll) + INDEX.
RememberCreate Index + the query planner (a big table).
UseCreate Index in a big table (lookup by roll).
TrapCreate Index — indexing a low-cardinality flag only.
ExCreate Index → lookup by roll.

CREATE TABLE authors ( id INTEGER PRIMARY KEY, email TEXT UNIQUE NOT NULL ); CREATE TABLE books ( id INTEGER PRIMARY KEY, author_id INTEGER NOT NULL, title TEXT NOT NULL, FOREIGN KEY (author_id) REFERENCES authors(id) ); INSERT INTO authors VALUES (1, 'a@x.com'); INSERT INTO books VALUES (1, 1, 'SQL Basics'); SELECT * FROM books;

Create Index

Create Index — sample query

Short notes

Questions

Create Index

Create Index — sample query

Short notes

Questions