SQL · Theory

Index Type

← All stacks

Theory

414/439

Index Type

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

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

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

Isha ships Index Type in a big table. That is the use case worth saying.

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

Place Index Type next to nearby SQL work — PRIMARY KEY(roll) + INDEX is the link.

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

Exam tip

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

Index Type — 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;

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

Short notes

DefIndex Type — Isha uses it for lookup by roll in a big table.
RuleIndex Type → PRIMARY KEY(roll) + INDEX.
RememberIndex Type + the query planner (a big table).
UseIndex Type in a big table (lookup by roll).
TrapIndex Type — indexing a low-cardinality flag only.
ExIndex Type → 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;

Index Type

Index Type — sample query

Short notes

Questions

Index Type

Index Type — sample query

Short notes

Questions