Lesson 114 of 12118 min read

CREATE INDEX in SQL with Single and Multi-Column Examples

Learn the CREATE INDEX syntax for both single-column and multi-column (composite) indexes, with practical MySQL examples.

Author: CodersNexus

CREATE INDEX in SQL with Single and Multi-Column Examples

Creating an index in MySQL follows a simple, consistent syntax whether you're indexing a single column or several columns together. This lesson covers the exact CREATE INDEX syntax, along with practical naming conventions and the composite index column-order consideration introduced in the previous lesson.

Key Definitions

  • CREATE INDEX: The SQL statement used to add a new index to an existing table's column or columns.
  • Index naming convention: A consistent naming pattern for indexes, commonly prefixed with idx_, followed by the table and column name(s), improving readability in schema management.

What You'll Learn

  • Write the CREATE INDEX syntax for a single-column index.
  • Write the CREATE INDEX syntax for a composite (multi-column) index.
  • Create a unique index using the CREATE UNIQUE INDEX variant.
  • Apply sensible index naming conventions.

Detailed Explanation

The basic syntax for a single-column index is `CREATE INDEX index_name ON table_name(column_name)`. For example, `CREATE INDEX idx_doctors_salary ON doctors(salary)` builds a B-Tree index on the salary column, speeding up queries that filter or sort by salary.

A composite index follows nearly identical syntax, simply listing multiple columns in order: `CREATE INDEX index_name ON table_name(column1, column2)`. As covered in the previous lesson, column order in this list matters significantly — placing the column most commonly used in isolation, or most selective (having many distinct values), first is generally the recommended approach, since the composite index can efficiently serve queries filtering on that leftmost column alone, in addition to queries filtering on the full combination.

To enforce uniqueness while also gaining the performance benefits of an index, use `CREATE UNIQUE INDEX index_name ON table_name(column_name)`, which behaves identically to a regular index for query performance purposes, but additionally rejects any INSERT or UPDATE that would introduce a duplicate value.

A widely-adopted naming convention prefixes every index name with idx_, followed by the table name and the indexed column(s), such as idx_doctors_salary or idx_doctors_dept_name for a composite index — this makes it immediately clear, just from the name, which table and columns any given index applies to, which becomes valuable as a schema accumulates dozens of indexes over time.

Visual Summary

Three syntax template boxes stacked vertically: [CREATE INDEX idx_name ON table(column);] labeled 'Single-column index', [CREATE INDEX idx_name ON table(col1, col2);] labeled 'Composite index — column order matters', [CREATE UNIQUE INDEX idx_name ON table(column);] labeled 'Unique index — adds a uniqueness constraint'.

Quick Reference

SyntaxPurpose
CREATE INDEX idx_name ON table(col)Single-column index for faster lookups/sorts on that column
CREATE INDEX idx_name ON table(col1, col2)Composite index for queries filtering/sorting on both columns
CREATE UNIQUE INDEX idx_name ON table(col)Index that also enforces no duplicate values

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

-- Single-column index: speeds up queries filtering or sorting by salary
CREATE INDEX idx_doctors_salary ON doctors(salary);

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

-- Unique index: enforces uniqueness while also speeding up lookups
ALTER TABLE doctors ADD COLUMN email VARCHAR(150);
CREATE UNIQUE INDEX idx_doctors_email ON doctors(email);

-- Verify the index is used for a query filtering on the composite index's leftmost column
EXPLAIN SELECT * FROM doctors WHERE department_id = 1;

idx_doctors_salary speeds up any query filtering or sorting on salary alone. idx_doctors_dept_name, the composite index, efficiently serves queries filtering on department_id alone (its leftmost column) as well as queries filtering on both department_id and doctor_name together, but would not efficiently serve a query filtering only on doctor_name in isolation. idx_doctors_email both enforces uniqueness on the email column and provides fast lookup performance for queries filtering by email.

Real-World Examples

  • Database administrators routinely run CREATE INDEX as part of performance tuning after identifying slow queries through EXPLAIN analysis or slow query logs.
  • User registration systems create a unique index on username or email columns immediately upon table creation, combining data integrity enforcement with lookup performance in one step.
  • E-commerce search filters commonly use composite indexes matching their most frequent filter combinations, such as (category_id, price), to keep filtered browsing fast.

Common Mistakes to Avoid

  • Placing composite index columns in the wrong order relative to actual query filtering patterns.
  • Not using a consistent index naming convention, making schema management confusing as indexes accumulate.
  • Creating a regular index when a unique index was actually needed to enforce a genuine data integrity constraint.

Interview Questions

Q1. What is the basic syntax for creating an index in MySQL?

CREATE INDEX index_name ON table_name(column_name), which can list a single column for a simple index or multiple columns, separated by commas, for a composite index.

Q2. How do you create an index that also enforces uniqueness?

Use CREATE UNIQUE INDEX index_name ON table_name(column_name), which builds a standard index for performance while additionally rejecting any INSERT or UPDATE that would introduce a duplicate value in that column.

Q3. Why does column order matter when creating a composite index?

A composite index efficiently serves queries filtering on its leftmost column, or leftmost combination of columns, so placing the most frequently filtered-on or most selective column first maximizes how many query patterns the index can usefully serve.

Practice MCQs

1. What is the correct syntax to create a composite index on department_id and doctor_name?

  1. CREATE INDEX idx ON doctors(department_id) AND doctors(doctor_name)
  2. CREATE INDEX idx ON doctors(department_id, doctor_name)
  3. CREATE COMPOSITE INDEX idx ON doctors
  4. CREATE INDEX idx ON doctors WHERE department_id, doctor_name

Answer: B. CREATE INDEX idx ON doctors(department_id, doctor_name)

Explanation: A composite index lists all its columns together, comma-separated, within a single set of parentheses in the CREATE INDEX statement.

2. What does CREATE UNIQUE INDEX do differently from a regular CREATE INDEX?

  1. It creates a faster index
  2. It additionally rejects duplicate values in the indexed column
  3. It only works on primary keys
  4. It cannot be used for lookups

Answer: B. It additionally rejects duplicate values in the indexed column

Explanation: A unique index provides the same lookup performance benefits as a regular index, while also enforcing that no two rows can share the same value in the indexed column.

Quick Revision Points

  • CREATE INDEX index_name ON table(column) is the basic syntax; list multiple columns for a composite index.
  • CREATE UNIQUE INDEX adds a uniqueness constraint alongside standard index performance benefits.
  • A consistent naming convention (like idx_table_column) is a widely recommended best practice.

Conclusion

  • CREATE INDEX builds a new index on one or more columns of an existing table.
  • Composite indexes list multiple columns together, with column order significantly affecting which queries benefit.
  • CREATE UNIQUE INDEX combines performance benefits with a genuine uniqueness data integrity constraint.

Creating an index in MySQL follows the straightforward syntax CREATE INDEX index_name ON table_name(column), extendable to composite indexes by listing multiple columns together, where column order significantly affects which query patterns the index can efficiently serve. CREATE UNIQUE INDEX additionally enforces that no duplicate values can exist in the indexed column, combining data integrity with performance benefits in one statement. A consistent naming convention, typically prefixing index names with idx_, keeps a growing schema's indexes clearly organized and easy to manage.

Frequently Asked Questions

CREATE INDEX index_name ON table_name(column_name) creates a basic single-column index; listing multiple comma-separated columns creates a composite index instead.

List the desired columns together, in order, within the parentheses of a CREATE INDEX statement, such as CREATE INDEX idx_name ON table(column1, column2).

Use CREATE UNIQUE INDEX index_name ON table_name(column_name), which builds a standard index while also enforcing that no duplicate values can exist in that column.

Yes, a composite index efficiently serves queries filtering on its leftmost column or combination of leftmost columns, so placing the most frequently used or most selective column first is generally recommended.

A common convention prefixes index names with idx_, followed by the table name and indexed column(s), such as idx_doctors_salary, making the index's purpose clear from its name alone.