SQL CREATE INDEX

SQL CREATE INDEX Statement

The SQL CREATE INDEX statement is used to create an index on a table. This command is essential for improving the speed and efficiency of data retrieval operations by allowing the database to find rows more quickly.

---

Syntax

-- To create an index on a table
CREATE INDEX index_name
ON table_name (column1, column2, ...);

-- To create a unique index on a table
CREATE UNIQUE INDEX index_name
ON table_name (column1, column2, ...);

• CREATE INDEX index_name: This is the SQL syntax used to create a non-unique index.
• CREATE UNIQUE INDEX index_name: This is the SQL syntax used to create a unique index, ensuring all values in the indexed columns are unique.
• ON table_name: This specifies the name of the table on which the index is created.
• column1, column2, ...: These specify the names of the columns to include in the index.

---

Example

Let's go through a complete example that includes creating a database, creating a table, inserting data into the table, and then creating an index on the table.

Step 1: Creating a Database

This step involves creating a new database named example_db.

CREATE DATABASE example_db;

In this example, we create a database named example_db.

Step 2: Creating a Table

In this step, we create a table named employees within the previously created database.

USE example_db;

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100),
    hire_date DATE
);

Here, we define the employees table with columns for id, first_name, last_name, email, and hire_date. The id column is set as the primary key and will auto-increment.

Step 3: Inserting Data into the Table

This step involves inserting some sample data into the employees table.

INSERT INTO employees (first_name, last_name, email, hire_date) VALUES ('John', 'Doe', 'john.doe@example.com', '2023-01-01');
INSERT INTO employees (first_name, last_name, email, hire_date) VALUES ('Jane', 'Smith', 'jane.smith@example.com', '2023-02-01');
INSERT INTO employees (first_name, last_name, email, hire_date) VALUES ('Alice', 'Johnson', 'alice.johnson@example.com', '2023-03-01');
INSERT INTO employees (first_name, last_name, email, hire_date) VALUES ('Bob', 'Brown', 'bob.brown@example.com', '2023-04-01');

Here, we insert four rows of data into the employees table.

Step 4: Creating an Index

This step involves creating an index on the last_name column of the employees table to improve the speed of queries that filter by last name.

CREATE INDEX idx_last_name
ON employees (last_name);

This command creates an index named idx_last_name on the last_name column of the employees table.

Step 5: Creating a Unique Index

This step involves creating a unique index on the email column of the employees table to ensure all email addresses are unique.

CREATE UNIQUE INDEX idx_email
ON employees (email);

This command creates a unique index named idx_email on the email column of the employees table, ensuring that all email addresses are unique.