Index in database

Indexes are database objects used to improve the retrieval performance of data from tables by providing efficient access paths to the data. In SQL databases, there are several types of indexes, each with its own purpose and advantages. Here, I'll explain the most common types of indexes with examples and SQL code:

1. Single-Column Index:

   • A single-column index is created on a single column of a table.

   Example:

   CREATE INDEX idx_lastname ON employees (last_name);

   In this example, we create an index named idx_lastname on the last_name column of the employees table.

2. Composite Index:

   • A composite index is created on multiple columns of a table.

   Example:

   CREATE INDEX idx_fullname ON employees (last_name, first_name);

   Here, we create an index named idx_fullname on both the last_name and first_name columns of the employees table. Composite indexes are useful for queries that involve multiple columns.

3. Unique Index:

   • A unique index enforces the uniqueness constraint on the indexed column(s), preventing duplicate values.

   Example:

   CREATE UNIQUE INDEX idx_email ON users (email);

   This creates a unique index named idx_email on the email column of the users table. It ensures that no two rows can have the same email address.

4. Clustered Index (SQL Server) or Primary Key (most databases):

   • A clustered index determines the physical order of data rows in a table. In most databases, a table can have only one clustered index.

   Example:

   CREATE CLUSTERED INDEX idx_employees_id ON employees (employee_id);

   In SQL Server, this creates a clustered index named idx_employees_id on the employee_id column of the employees table. Rows in the table will be physically ordered by employee_id.

5. Non-Clustered Index:

   • A non-clustered index is a separate structure from the table data and provides a logical order for quick data retrieval.

   Example:

   CREATE NONCLUSTERED INDEX idx_salary ON employees (salary);

   This creates a non-clustered index named idx_salary on the salary column of the employees table. It allows for efficient lookups based on salary values.

6. Full-Text Index (SQL Server and some other databases):

   • A full-text index is used for searching text data efficiently, providing features like keyword searching and proximity searches.

   Example:

   CREATE FULLTEXT INDEX idx_product_description ON products (product_description);

   This creates a full-text index named idx_product_description on the product_description column of the products table. It's useful for text-based search queries.

7. Spatial Index (for spatial databases like PostgreSQL with PostGIS):

   • A spatial index is used to optimize spatial queries on geographic or geometric data.

   Example:

   CREATE INDEX idx_geom_location ON spatial_data USING GIST (geom);

   This creates a spatial index named idx_geom_location on the geom column of the spatial_data table using the GIST index type.

8. Bitmap Index (used in some databases for specific cases):

   • A bitmap index stores a bitmap for each distinct value in a column. It's suitable for columns with low cardinality.

   Example:

   CREATE BITMAP INDEX idx_category ON products (category);

   Here, we create a bitmap index named idx_category on the category column of the products table.

These are some of the common types of indexes you may encounter in SQL databases. The choice of index type depends on the specific requirements of your application and the types of queries you need to optimize.