SQL Index Tutorial

Example Table

We will create a table named employees to demonstrate SQL indexing:

        CREATE TABLE employees (
            employee_id INT PRIMARY KEY,
            first_name VARCHAR(50),
            last_name VARCHAR(50),
            department VARCHAR(50)
        );
        

Creating the Table

To create the table, use the following SQL command:

        CREATE TABLE employees (
            employee_id INT PRIMARY KEY,
            first_name VARCHAR(50),
            last_name VARCHAR(50),
            department VARCHAR(50)
        );
        

Result:

Command	CREATE TABLE employees (employee_id INT PRIMARY KEY, first_name VARCHAR(50), last_name VARCHAR(50), department VARCHAR(50));
Result	A table named employees is created.

Creating an Index

To create an index on the last_name column, use the following SQL command:

        CREATE INDEX idx_last_name ON employees (last_name);
        

Result:

Command	CREATE INDEX idx_last_name ON employees (last_name);
Result	An index named idx_last_name is created on the last_name column of the employees table.

Querying Data with Index

To query data using the index, use the following SQL command:

        SELECT * FROM employees WHERE last_name = 'Smith';
        

Result:

Command	SELECT * FROM employees WHERE last_name = 'Smith';
Result	Returns all rows from the employees table where the last_name is 'Smith'. The query performance is improved due to the index.

Important Considerations

When creating indexes, consider the following:

• Indexes improve query performance but can slow down data modification operations (INSERT, UPDATE, DELETE).
• Use indexes on columns that are frequently used in WHERE clauses.
• Avoid creating too many indexes on a single table as it can negatively impact performance.