SQL Index

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:

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:

Querying Data with Index

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

        SELECT * FROM employees WHERE last_name = 'Smith';
        

Result:

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.