SQL · Theory

Create Index

Theory

77/439

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.

Exam tip

CREATE INDEX idx_city ON students(city); EXPLAIN the SELECT.

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.

Short notes

DefIndex speeds lookups on that column. PK is an index.
RuleIndex columns you filter/join. EXPLAIN to check.
TrapToo many indexes. Function on column in WHERE.

SQL · Theory

Create Index

← All stacks

Theory

77/439

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.

Exam tip

CREATE INDEX idx_city ON students(city); EXPLAIN the SELECT.

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.

Short notes

DefIndex speeds lookups on that column. PK is an index.
RuleIndex columns you filter/join. EXPLAIN to check.
TrapToo many indexes. Function on column in WHERE.

Create Index

Create Index — sample query

Short notes

Questions

Create Index

Create Index — sample query

Short notes

Questions