SQL · Theory

Force Index

← All stacks

Theory

431/439

Force Index

Force Index is simple if you keep it on paper. Amit uses it for lookup by roll in a big table.

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

Without Force Index, a big table gets messy and lookup by roll is hard to trust.

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

Force Index trap — indexing a low-cardinality flag only. Amit loses marks for that every viva.

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

Viva for Force Index — what it is → PRIMARY KEY(roll) + INDEX → the mistake (indexing a low-cardinality flag only).

Exam tip

Board: lookup by roll. Dry-run Force Index. Name the trap: indexing a low-cardinality flag only.

Force 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;

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

Short notes

DefForce Index — Amit uses it for lookup by roll in a big table.
RuleForce Index → PRIMARY KEY(roll) + INDEX.
RememberForce Index + InnoDB (a big table).
UseForce Index in a big table (lookup by roll).
TrapForce Index — indexing a low-cardinality flag only.
ExForce 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;

Force Index

Force Index — sample query

Short notes

Questions

Force Index

Force Index — sample query

Short notes

Questions