Drop Index — SQL Technical Interview Questions & Answers

Drop Index

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.

Technical Interview Tip: DROP INDEX idx_city ON students;

Code Examples for Drop Index

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.

How to Explain Drop Index to an Interviewer

How do you remove an index?

DROP INDEX idx ON table;