9.4 Indexes

An index is an auxiliary data structure that speeds up data retrieval at the cost of additional storage and slower write operations. Without an index, the DBMS must perform a FULL TABLE SCAN — reading every row to find matches.

 

ANALOGY: An index in a database is like the index at the back of a textbook. Instead of reading the entire book to find where 'normalisation' is discussed, you look it up in the index and go directly to page 145. This is the difference between O(n) and O(log n) performance.

 

Index Type

Description and Use Case

B-Tree Index (default)

Balanced tree structure. Good for range queries (BETWEEN, >, <) and equality searches. Most common type in MySQL.

Hash Index

Uses a hash table. Very fast for equality searches (=) only. Not useful for range queries. Used in MEMORY tables.

Full-Text Index

For text searching (MATCH...AGAINST). Useful for product descriptions, article content.

Composite Index

Index on multiple columns. Order matters: INDEX(LastName, FirstName) helps queries on LastName alone or (LastName, FirstName) together, but NOT FirstName alone.

Unique Index

Ensures no duplicate values. MySQL creates one automatically for PRIMARY KEY and UNIQUE constraints.

Clustered Index

Rows are physically stored in index order. Each table has at most one clustered index (the PRIMARY KEY in MySQL InnoDB).

 

SQL COMMANDS FOR INDEXES:

-- Create an index on LastName for faster name searches

CREATE INDEX idx_lastname ON STUDENT(LastName);

 

-- Create a composite index for queries filtering by State and AccountType

CREATE INDEX idx_state_accttype ON CUSTOMER(State, AccountType);

 

-- Drop an index

DROP INDEX idx_lastname ON STUDENT;

 

-- Show all indexes on a table

SHOW INDEX FROM STUDENT;