SQL CREATE INDEX Statement

 The CREATE INDEX statement in SQL is used to create an index on one or more columns of a table. An index improves the speed of data retrieval operations, but it can slow down data modification operations (INSERT, UPDATE, DELETE) because the index itself must also be updated.

Syntax

CREATE [UNIQUE] INDEX index_name
ON table_name (column1, column2, ...);

• UNIQUE: Optional. If specified, the index will enforce uniqueness of the values in the columns.
• index_name: The name of the index being created.
• table_name: The name of the table on which the index is created.
• column1, column2, ...: The columns on which the index will be created. You can index one or more columns.

Example 1: Simple Index

CREATE INDEX idx_employee_lastname
ON employees (lastname);

This creates an index named idx_employee_lastname on the lastname column of the employees table. This will improve the performance of queries that filter or sort by lastname.

Example 2: Unique Index

CREATE UNIQUE INDEX idx_employee_email
ON employees (email);

This creates a unique index on the email column of the employees table. The UNIQUE constraint ensures that no two employees can have the same email.

Example 3: Composite Index

CREATE INDEX idx_employee_name_age
ON employees (firstname, lastname, age);

This creates a composite index on the firstname, lastname, and age columns of the employees table. Composite indexes are useful when you frequently query using multiple columns.

Example 4: Full-Text Index (for text-based searches)

In some database systems like MySQL, PostgreSQL, and SQL Server, you can create a full-text index for text-based searches.

CREATE FULLTEXT INDEX idx_fulltext_description
ON products (description);

Example 5: Index on Expressions (Database Dependent)

Some databases allow creating indexes on expressions or computed columns. For example:

CREATE INDEX idx_lowercase_lastname
ON employees (LOWER(lastname));

This would create an index on the lowercase version of the lastname column, useful for case-insensitive searches.

Dropping an Index

If you no longer need an index, you can remove it with the DROP INDEX statement:

DROP INDEX idx_employee_lastname;

Important Notes:

1. Indexes and Performance: Indexes improve SELECT query performance but can degrade performance for INSERT, UPDATE, and DELETE operations since the index must be maintained.
2. Unique Index: If you need to enforce uniqueness in the table (i.e., no two rows can have the same value for specific columns), use the UNIQUE index.
3. Composite Index: A composite index is useful when you frequently query using multiple columns, but keep in mind that the order of the columns in the index is significant for the query's performance.

Always analyze your database workload before adding too many indexes, as maintaining them incurs overhead.