SQL · Theory

Drop Index

Theory

78/439

Drop Index

DROP INDEX idx_city ON students; or ALTER TABLE students DROP INDEX idx_city; You cannot DROP the PRIMARY KEY with DROP INDEX that way — use ALTER TABLE DROP PRIMARY KEY (and have a plan).

Drop unused indexes to speed writes. Confirm with EXPLAIN that a slow query was not using it.

Trap — dropping an index that a UNIQUE constraint needs — uniqueness goes too.

On the example next to this theory — Drop Index — keys and constraints protect data integrity; indexes speed lookups.

Exam tip

DROP INDEX idx_city ON students;

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 name ON table;
RuleDon’t drop UNIQUE/PK unless you mean to.
TrapDropping an index a query still needs.

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