Create Index — SQL Technical Interview Questions & Answers

Create Index

Create Index

  • An index is a lookup list. CREATE INDEX idx_city ON students(city); makes WHERE city = 'Pune' and JOIN … ON city faster. PRIMARY KEY is already an index.
  • Indexes speed reads and slow writes (INSERT/UPDATE/DELETE must maintain the index). Don’t index every column. Low-cardinality flags (yes/no) often help little.
  • EXPLAIN SELECT …; shows whether MySQL uses the index (key column). If type=ALL, it scanned the table.
  • Trap: indexing a column you never filter on. Trap: functions on the column (WHERE YEAR(dob)=2004) that ignore the index.
  • On the example next to this theory: Create Index — keys and constraints protect data integrity; indexes speed lookups.

Technical Interview Tip: CREATE INDEX idx_city ON students(city); EXPLAIN the SELECT.

Code Examples for Create Index

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

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

How to Explain Create Index to an Interviewer

Why an index?

Faster WHERE/JOIN Slower writes

How do you check?

EXPLAIN SELECT …