SQL · Theory

Drop Index

← All stacks

Theory

78/439

Drop Index

If you can teach Drop Index using lookup by roll, you know it. If you only know the heading, you don’t.

Do Drop Index once by hand. PRIMARY KEY(roll) + INDEX. Change one input. Say the new result out loud.

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

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

Don’t do this with Drop Index — indexing a low-cardinality flag only. Interviewers spot it in ten seconds.

Drop Index is not a lonely heading. Amit ties it to lookup by roll.

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

Exam tip

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

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

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

Short notes

DefDrop Index — Amit uses it for lookup by roll in a big table.
RuleDrop Index → PRIMARY KEY(roll) + INDEX.
RememberDrop Index + InnoDB (a big table).
UseDrop Index in a big table (lookup by roll).
TrapDrop Index — indexing a low-cardinality flag only.
ExDrop 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;

Drop Index

Drop Index — sample query

Short notes

Questions

Drop Index

Drop Index — sample query

Short notes

Questions