SQL Index Types: Primary, Unique, Fulltext, Spatial and Composite

Not every index serves the same purpose. MySQL offers several distinct index types, each optimized for a different kind of query — from enforcing uniqueness, to searching free-text content, to efficiently filtering on multiple columns together. Choosing the right index type for a given problem is just as important as knowing to add an index in the first place.

Key Definitions

• Primary key index: The special, mandatory index automatically created on a table's primary key column, enforcing both uniqueness and non-null values.
• Unique index: An index that enforces that no two rows can share the same value in the indexed column(s), without the additional constraints a primary key carries.
• Fulltext index: A specialized index designed for efficient free-text search within large text columns, supporting natural-language-style queries beyond simple equality matching.
• Spatial index: A specialized index designed for efficient querying of geographic or geometric data types, such as coordinates.
• Composite index (multi-column index): An index built across two or more columns together, optimized for queries that filter or sort on that specific combination of columns.

What You'll Learn

• Distinguish between primary key, unique, and regular (non-unique) indexes.
• Understand fulltext indexes for text search and spatial indexes for geographic data.
• Define composite (multi-column) indexes and when they're needed.
• Match a real query scenario to the most appropriate index type.

Detailed Explanation

Every table's primary key automatically receives a primary key index, which is both unique (no duplicate values allowed) and enforces that the column can never be NULL — this is the index MySQL relies on most heavily for enforcing entity identity. A unique index is similar in enforcing no duplicates, but without the NOT NULL requirement a primary key carries, and a table can have multiple unique indexes (unlike having only one primary key) — for example, adding a UNIQUE index on doctors.email ensures no two doctors share the same email, independent of the doctor_id primary key.

A fulltext index is purpose-built for searching within large blocks of text, such as searching a medical_notes column for the word 'hypertension' anywhere within lengthy doctor notes. A standard B-Tree index isn't well-suited for this kind of 'contains this word anywhere' search; a fulltext index uses specialized text-search algorithms instead, enabling MySQL's MATCH() AGAINST() syntax for natural-language-style queries.

A spatial index is specifically designed for geographic or geometric data types, such as storing and efficiently querying hospital branch locations by latitude and longitude, supporting queries like 'find all branches within 10 kilometers of this point' far more efficiently than a standard index could.

A composite index spans multiple columns together, such as an index on (department_id, doctor_name), optimized specifically for queries that filter on department_id and doctor_name together, or that filter on department_id alone (since a composite index can still be used for queries on its leftmost column), but not for queries filtering only on doctor_name in isolation — a nuance covered in more depth in Lesson 9.9.

Visual Summary

A table with five rows, each representing an index type, its icon, and its ideal use case: [Primary Key Index — 🔑 — uniquely identifies each row], [Unique Index — 🚫duplicate — enforces column uniqueness beyond the primary key], [Fulltext Index — 🔍text — searching within large text blocks], [Spatial Index — 🗺️ — geographic coordinate queries], [Composite Index — 📊multi-col — filtering/sorting on multiple columns together].

Quick Reference

Index Type	Enforces Uniqueness?	Best For
Primary key index	Yes (and NOT NULL)	The table's main identity column
Unique index	Yes	Secondary unique constraints, like email
Fulltext index	No	Searching within large text columns
Spatial index	No	Geographic/geometric coordinate queries
Composite index	Depends on definition	Filtering/sorting on multiple columns together

SQL Example

CREATE TABLE departments (
  department_id   INT PRIMARY KEY AUTO_INCREMENT,
  department_name VARCHAR(100) NOT NULL
);

CREATE TABLE doctors (
  doctor_id       INT PRIMARY KEY AUTO_INCREMENT,
  doctor_name     VARCHAR(100) NOT NULL,
  department_id   INT,
  salary          INT,
  FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

CREATE TABLE patients (
  patient_id      INT PRIMARY KEY AUTO_INCREMENT,
  patient_name    VARCHAR(100) NOT NULL,
  city            VARCHAR(80)
);

CREATE TABLE appointments (
  appointment_id    INT PRIMARY KEY AUTO_INCREMENT,
  patient_id        INT,
  doctor_id         INT,
  appointment_date  DATE,
  status            VARCHAR(20),
  FOREIGN KEY (patient_id) REFERENCES patients(patient_id),
  FOREIGN KEY (doctor_id)  REFERENCES doctors(doctor_id)
);

CREATE TABLE bills (
  bill_id         INT PRIMARY KEY AUTO_INCREMENT,
  appointment_id  INT,
  amount          DECIMAL(10,2),
  paid            BOOLEAN DEFAULT FALSE,
  FOREIGN KEY (appointment_id) REFERENCES appointments(appointment_id)
);

INSERT INTO departments VALUES
  (1, 'Cardiology'), (2, 'Orthopedics'), (3, 'Neurology'), (4, 'Dermatology');

INSERT INTO doctors (doctor_id, doctor_name, department_id, salary) VALUES
  (101, 'Dr. Verma', 1, 95000), (102, 'Dr. Iyer', 2, 78000),
  (103, 'Dr. Sen', 3, 120000), (104, 'Dr. Khan', 1, 88000);

INSERT INTO patients VALUES
  (201, 'Amit Rao', 'Pune'), (202, 'Neha Joshi', 'Mumbai'),
  (203, 'Karan Mehta', 'Delhi'), (204, 'Divya Nair', 'Pune');

INSERT INTO appointments VALUES
  (301, 201, 101, '2026-05-01', 'Completed'),
  (302, 202, 102, '2026-05-02', 'Completed'),
  (303, 203, 101, '2026-05-03', 'Cancelled'),
  (304, 204, 103, '2026-05-04', 'Completed');

INSERT INTO bills (appointment_id, amount, paid) VALUES
  (301, 1500.00, TRUE), (302, 2200.00, FALSE),
  (303, 800.00, TRUE), (304, 3000.00, TRUE);

-- Unique index: no two doctors can share the same email
ALTER TABLE doctors ADD COLUMN email VARCHAR(150);
CREATE UNIQUE INDEX idx_doctors_email ON doctors(email);

-- Composite index: optimized for queries filtering on department_id AND doctor_name together
CREATE INDEX idx_doctors_dept_name ON doctors(department_id, doctor_name);

-- Fulltext index: efficient searching within a large text column
ALTER TABLE doctors ADD COLUMN medical_notes TEXT;
CREATE FULLTEXT INDEX idx_doctors_notes ON doctors(medical_notes);

SELECT doctor_name FROM doctors
WHERE MATCH(medical_notes) AGAINST('hypertension');

The unique index on email guarantees no duplicate doctor emails can ever be inserted, independent of doctor_id. The composite index on (department_id, doctor_name) is specifically optimized for queries filtering on both columns together, such as finding all doctors named 'Dr. Verma' within a specific department. The fulltext index on medical_notes enables the specialized MATCH() AGAINST() syntax, efficiently searching for the word 'hypertension' anywhere within potentially lengthy medical notes text, something a standard B-Tree index could not do efficiently.

Real-World Examples

• User authentication systems universally use a unique index on the email or username column to enforce account uniqueness at the database level.
• Content platforms and search engines rely on fulltext indexes to power article, product, or document search functionality efficiently.
• Ride-sharing and delivery apps use spatial indexes to efficiently find nearby drivers or delivery partners within a given radius of a pickup location.

Common Mistakes to Avoid

• Using a standard index for large text search instead of a fulltext index, resulting in poor search performance.
• Confusing a unique index with a primary key, missing that a table can have several unique indexes but only one primary key.
• Creating a composite index in the wrong column order for the actual query patterns it needs to support.

Interview Questions

Q1. What is the difference between a primary key index and a unique index?

Both enforce that no duplicate values exist in the indexed column, but a primary key index additionally enforces that the column can never be NULL, and a table can have only one primary key while it can have multiple unique indexes.

Q2. When would you use a fulltext index instead of a standard index?

When you need to efficiently search for words or phrases anywhere within a large text column, such as searching notes, articles, or descriptions, since a standard B-Tree index isn't well-suited for this kind of natural-language 'contains' search.

Q3. What is a composite index, and why does column order matter within it?

A composite index spans multiple columns together, optimized for queries filtering or sorting on that specific combination. Column order matters because a composite index can efficiently serve queries filtering on its leftmost column(s), but generally cannot efficiently serve a query that only filters on a non-leftmost column in isolation.

Practice MCQs

1. Which index type is best suited for searching within large blocks of text?

1. Primary key index
2. Spatial index
3. Fulltext index
4. Composite index

Answer: C. Fulltext index

Explanation: A fulltext index uses specialized text-search algorithms specifically designed for efficiently finding words or phrases within large text columns.

2. What is the key difference between a primary key index and a unique index?

1. There is no difference
2. A primary key index also enforces NOT NULL, and a table can have only one primary key
3. Unique indexes are faster than primary key indexes
4. Primary key indexes cannot be used for lookups

Answer: B. A primary key index also enforces NOT NULL, and a table can have only one primary key

Explanation: While both enforce uniqueness, the primary key additionally requires non-null values and a table can have only one primary key, while multiple unique indexes are allowed.

Quick Revision Points

• Primary key indexes enforce uniqueness and NOT NULL; unique indexes enforce only uniqueness.
• Fulltext indexes are for text search; spatial indexes are for geographic data.
• Composite index column order matters — it efficiently serves queries on its leftmost column(s).

Conclusion

• MySQL offers several index types, each optimized for a different query pattern: primary key, unique, fulltext, spatial, and composite.
• Choosing the right index type matters as much as deciding to add an index at all.
• Composite index column order significantly affects which queries it can efficiently serve.

MySQL provides several distinct index types suited to different query needs: primary key indexes (unique and NOT NULL, for the table's core identity column), unique indexes (enforcing uniqueness on secondary columns like email), fulltext indexes (for efficient searching within large text columns), spatial indexes (for geographic coordinate data), and composite indexes (spanning multiple columns together for combined filtering or sorting). Selecting the appropriate index type for a given query pattern — rather than defaulting to a single generic approach — is essential for genuinely effective database performance tuning.