SQL CREATE INDEX Keyword

 The CREATE INDEX keyword in SQL is used to create an index on a database table. Indexes are used to speed up the retrieval of rows from the table, especially when dealing with large datasets. They can be created on one or more columns of a table.

Syntax:

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

Parameters:

• index_name: The name you want to give the index. It should be unique within the database.
• table_name: The name of the table where the index will be created.
• column1, column2, ...: The columns in the table on which the index will be created.

Example 1: Creating an Index on a Single Column

CREATE INDEX idx_customer_lastname
ON customers (lastname);

This creates an index called idx_customer_lastname on the lastname column of the customers table.

Example 2: Creating an Index on Multiple Columns

CREATE INDEX idx_customer_name
ON customers (lastname, firstname);

This creates a composite index on the lastname and firstname columns of the customers table.

Types of Indexes:

• Unique Index: Ensures that the indexed columns have unique values.

  CREATE UNIQUE INDEX idx_customer_email
  ON customers (email);
  

• Full-text Index: Used for text-based searching, available in some databases like MySQL and PostgreSQL.

Benefits:

• Increases the speed of query performance, especially for SELECT, JOIN, WHERE, and ORDER BY operations.

Considerations:

• Indexes take up additional space in the database.
• Too many indexes can slow down INSERT, UPDATE, and DELETE operations because indexes need to be updated as well.