SQL CREATE INDEX

Introduction

In this chapter, we will focus on the CREATE INDEX statement in SQL. Indexes are special database objects that can greatly improve the performance of data retrieval operations. This chapter will cover the definition, types, syntax, and provide examples to help you understand how to use the CREATE INDEX statement effectively.

What is an Index?

An index is a database object that improves the speed of data retrieval operations on a table at the cost of additional storage space and maintenance overhead. Indexes work by creating a data structure (typically a B-tree) that allows the database to find rows more quickly than it could by scanning the entire table.

Types of Indexes

1. Unique Index: Ensures that all values in the indexed column are unique.
2. Non-Unique Index: Does not enforce uniqueness and allows duplicate values in the indexed column.
3. Composite Index: An index on two or more columns of a table.
4. Full-Text Index: Special type of index used for text-search queries.
5. Clustered Index: Determines the physical order of data in the table (only one clustered index per table).
6. Non-Clustered Index: Does not alter the physical order of the table and maintains a logical ordering of data.

Syntax for Creating Indexes

Basic Syntax

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

Unique Index

CREATE UNIQUE INDEX index_name
ON table_name (column);

Composite Index

CREATE INDEX index_name
ON table_name (column1, column2);

Full-Text Index (MySQL)

CREATE FULLTEXT INDEX index_name
ON table_name (column);

Clustered Index (SQL Server)

CREATE CLUSTERED INDEX index_name
ON table_name (column);

Non-Clustered Index (SQL Server)

CREATE NONCLUSTERED INDEX index_name
ON table_name (column);

Step-by-Step Example

Sample Table

First, let's create a sample table named employees.

CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100),
    department_id INT,
    salary DECIMAL(10, 2)
);

Inserting Sample Data

INSERT INTO employees (first_name, last_name, email, department_id, salary)
VALUES
('Ramesh', 'Kumar', 'ramesh.kumar@example.com', 1, 50000),
('Sita', 'Patel', 'sita.patel@example.com', 2, 60000),
('Arjun', 'Singh', 'arjun.singh@example.com', 1, 55000),
('Priya', 'Sharma', 'priya.sharma@example.com', 2, 62000),
('Ravi', 'Verma', 'ravi.verma@example.com', 3, 50000);

Creating an Index

To create an index on the last_name column:

CREATE INDEX idx_last_name
ON employees (last_name);

Creating a Unique Index

To create a unique index on the email column:

CREATE UNIQUE INDEX idx_email
ON employees (email);

Creating a Composite Index

To create a composite index on the first_name and last_name columns:

CREATE INDEX idx_name
ON employees (first_name, last_name);

Creating a Full-Text Index (MySQL)

To create a full-text index on the email column:

CREATE FULLTEXT INDEX idx_fulltext_email
ON employees (email);

Creating a Clustered Index (SQL Server)

To create a clustered index on the last_name column:

CREATE CLUSTERED INDEX idx_clustered_last_name
ON employees (last_name);

Creating a Non-Clustered Index (SQL Server)

To create a non-clustered index on the salary column:

CREATE NONCLUSTERED INDEX idx_nonclustered_salary
ON employees (salary);

Dropping an Index

To drop an index when it is no longer needed:

Dropping an Index in MySQL

DROP INDEX idx_last_name ON employees;

Dropping an Index in SQL Server

DROP INDEX employees.idx_last_name;

Viewing Indexes

To view indexes on a table, you can use the following commands:

MySQL

SHOW INDEX FROM employees;

SQL Server

EXEC sp_helpindex 'employees';

PostgreSQL

SELECT * FROM pg_indexes WHERE tablename = 'employees';

Conclusion

Indexes are essential for optimizing the performance of SQL queries. This chapter covered the basic syntax for creating various types of indexes, provided examples to illustrate their use, and demonstrated how to view and drop indexes. Understanding how to use indexes effectively will greatly enhance your ability to manage and query large datasets efficiently in a relational database. In the next chapter, we will explore more about DROP INDEX.