Indexing in database - Example of indexing

Indexing is a technique used in databases to improve the speed and efficiency of data retrieval operations. An index is a data structure that allows the database to find and access specific rows much faster than it could without the index. Here’s an overview of indexing and an example to illustrate how it works:

Key Concepts of Indexing:

1. Purpose:

   • Speed Up Queries: Indexes allow databases to quickly locate and retrieve the data you need without scanning the entire table.
   • Improve Performance: By reducing the amount of data the database needs to process, indexes can significantly improve query performance.

2. Types of Indexes:

   • Single-Column Index: An index created on a single column of a table.
   • Composite Index (Multi-Column Index): An index created on multiple columns of a table, often used when queries frequently filter based on multiple columns.
   • Unique Index: Ensures that all values in the indexed column(s) are unique.
   • Full-Text Index: Specialized index for text searching, enabling efficient searches within large text fields.

3. B-Tree Indexes:

   • Common Structure: Most relational databases use B-tree (or variants like B+ tree) structures for indexing. B-trees are balanced tree structures that maintain sorted data, allowing for fast search, insert, delete, and sequential access operations.

4. Considerations:

   • Storage Overhead: Indexes consume additional storage space and require maintenance, especially during insert, update, and delete operations.
   • Index Selectivity: High selectivity (the uniqueness of indexed values) generally leads to better performance. Indexes are most effective when they reduce the number of rows the database needs to scan.

Example of Indexing in a Database:

Let’s consider an example using a table of employee records in a relational database:

Example Table: employees

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    department VARCHAR(100),
    hire_date DATE,
    salary DECIMAL(10, 2)
);

Suppose we often query the employees table to find employees by their name and by department.

Creating Indexes:

1. Index on name Column:

   CREATE INDEX idx_employees_name ON employees(name);
   

   This index allows the database to quickly find employees based on their names. Queries like the following will benefit from this index:

   SELECT * FROM employees WHERE name = 'John Doe';
   

2. Index on department Column:

   CREATE INDEX idx_employees_department ON employees(department);
   

   This index speeds up queries filtering by department:

   SELECT * FROM employees WHERE department = 'Engineering';
   

3. Composite Index on department and hire_date Columns:

   CREATE INDEX idx_employees_department_hire_date ON employees(department, hire_date);
   

   This composite index is useful for queries that filter by both department and hire date:

   SELECT * FROM employees WHERE department = 'Engineering' AND hire_date > '2022-01-01';
   

Impact of Indexing:

• Query Performance: With the indexes in place, the database can quickly locate the relevant rows without scanning the entire employees table, leading to faster query performance.
• Storage and Maintenance: The indexes consume additional storage space and need to be updated whenever data in the indexed columns changes. This can slightly degrade the performance of insert, update, and delete operations.