Indexes

Indexes allow you to quickly find the information you need in tables. When creating an index for a specific field in a database, the system creates a separate structure that contains sorted values of that field along with references to the corresponding records. Data in an indexed column is stored in a specific order, which makes searching easier and faster: numeric data is stored in numerical order, text data in alphabetical order, and dates in chronological order. This allows the database to quickly find the required records.

Indexes should be used wisely. Every change in a table (UPDATE, INSERT, and DELETE) requires rebuilding indexes, which can slow down database operations. That is why indexes should only be created for fields that are searched much more frequently than they are written to.

The size of the database can also affect the practicality of using indexes. For example, with a small database, implementing indexes may not have a significant impact on performance. However, as the amount of data increases, indexes can significantly speed up read operations.

Creating indexes is a great technique for optimizing database performance, but use it consciously, as it only improves reading and negatively affects write speed.

Create Employees table:

CREATE DATABASE company;
USE company;
CREATE TABLE Employees (
    EmployeeID INT AUTO_INCREMENT,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Position VARCHAR(50),
    Department VARCHAR(50),
    HireDate DATE,
    PRIMARY KEY (EmployeeID)
);
DESCRIBE Employees;
INSERT INTO Employees (FirstName, LastName, Position, HireDate, Department)
VALUES ('John', 'Doe', 'Manager', '2023-01-10', 'Marketing'),
       ('Jane', 'Dough', 'Developer', '2023-02-20', 'IT');
SELECT * FROM Employees;

Creating an Index

As an example, let's look at the Employees table and create an index for the Department column. Indexes are created with the CREATE INDEX command:

CREATE INDEX idx_department ON Employees(Department);

Here idx_department is the index name, Employees is the table name, and Department is the name of the column whose data is being indexed.

Now let's view the existing indexes for our table. We will use the following command:

SHOW INDEX FROM Employees;

If needed, we can change the index name or delete it. Let's rename our index, for example, to current_department_index:

ALTER TABLE Employees RENAME INDEX idx_department TO current_department_index.

To delete an index, use the following command:

ALTER TABLE Employees DROP INDEX current_department_index.