SQL CREATE INDEX Statement

The SQL CREATE INDEX statement is used to create indexes in tables, which can improve the speed of data retrieval operations by allowing the database to find and retrieve rows more efficiently.

Basic Syntax

CREATE INDEX index_name
ON table_name (column1, column2, ...);

• index_name: Name of the index.
• table_name: Name of the table where the index is created.
• column1, column2, ...: Column or columns to be indexed.

Example 1: Creating a Simple Index

Let's say we have a table Employees with columns EmployeeID, FirstName, LastName, and Department.

CREATE TABLE Employees (
    EmployeeID int PRIMARY KEY,
    FirstName varchar(255),
    LastName varchar(255),
    Department varchar(255)
);

INSERT INTO Employees (EmployeeID, FirstName, LastName, Department) VALUES
(1, 'John', 'Doe', 'HR'),
(2, 'Jane', 'Smith', 'Finance'),
(3, 'Sam', 'Brown', 'IT');

To create an index on the LastName column:

CREATE INDEX idx_lastname
ON Employees (LastName);

This index will speed up queries that search for employees by their last name.

Example 2: Creating a Composite Index

A composite index is an index on multiple columns. For instance, if we frequently search for employees by their first and last names, we can create a composite index.

CREATE INDEX idx_name
ON Employees (FirstName, LastName);

This index will speed up queries like:

SELECT * FROM Employees
WHERE FirstName = 'Jane' AND LastName = 'Smith';

Example 3: Creating a Unique Index

A unique index ensures that the indexed columns do not contain duplicate values. Suppose we have a Users table where each username must be unique.

CREATE TABLE Users (
    UserID int PRIMARY KEY,
    Username varchar(255),
    Password varchar(255)
);

INSERT INTO Users (UserID, Username, Password) VALUES
(1, 'johndoe', 'password123'),
(2, 'janesmith', 'password456');

To create a unique index on the Username column:

CREATE UNIQUE INDEX idx_username
ON Users (Username);

This index will prevent duplicate usernames from being inserted into the table.

Example 4: Creating an Index with Specific Options

In some database systems, you can specify additional options when creating an index. For example, in MySQL, you can create a full-text index:

CREATE FULLTEXT INDEX idx_fulltext
ON Employees (FirstName, LastName);

This full-text index can improve the performance of text searches.

Example 5: Checking Indexes in a Table

To check the indexes in a table, you can use the following query:

SHOW INDEX FROM Employees;

This will return a list of all indexes on the Employees table, along with details about each index.

Summary

Creating indexes is a powerful way to improve database performance, especially for large tables and frequent queries. However, indexes also require additional storage and can impact write performance (INSERT, UPDATE, DELETE operations), so it's important to balance their use according to your specific needs.

If you have any more questions or need further examples, feel free to ask!