Create Index in MYSQL

Below are examples demonstrating the creation, dropping, and altering of an index in MySQL, along with altering a table to add a column:

1. Creation of an index:

-- Creating a table

CREATE TABLE employees (

    employee_id INT,

    first_name VARCHAR(50),

    last_name VARCHAR(50),

    hire_date DATE

);

-- Creating an index on the employee_id column

CREATE INDEX idx_employee_id ON employees(employee_id);

2. Dropping an index:

-- Dropping the previously created index

DROP INDEX idx_employee_id ON employees;

3. Altering a table to add a column:

-- Altering the table to add a new column

ALTER TABLE employees ADD email VARCHAR(100);

4. Recreation of the index with a different name:

-- Recreating the index with a different name

CREATE INDEX idx_employee_id_new ON employees(employee_id);

5. Altering an existing index:

MySQL doesn't provide a direct way to alter an existing index name. To rename an index, you would typically drop the existing index and then recreate it with the new name.

-- Dropping the existing index

DROP INDEX idx_employee_id_new ON employees;

-- Recreating the index with a new name

CREATE INDEX idx_employee_id_renamed ON employees(employee_id);

In this example:

- We create a table employees with columns employee_id, first_name, last_name, and hire_date.

- Then, we create an index named idx_employee_id on the employee_id column.

- We drop the index idx_employee_id.

- We alter the employees table to add a new column email.

- We recreate the index with a new name idx_employee_id_new.

These SQL statements demonstrate how you can create, drop, alter a table, and manage indexes in MySQL.