Overview

In this article, we will explore the Indexing in Database with Spring boot application. we will create the spring boot application and connect to the database. we will also create the index on the table and perform some operations to see the effect of indexing.

Introduction to Indexing in Database

Indexing is a mechanism used for increasing the speed of retrieving data from a database. Without indexing, if we search anything in a table based on some column value, it will search across all the rows in the table. But if we use indexing, then it will not check all rows of the table. Instead of doing this it will use the key to directly return that record.

When we create an index, it internally stores the key and value pointing to the actual data row. This improves performance, especially when we deal with large data.

Let’s create a Spring Boot application to explain indexing in a database step by step. I’ll guide you through creating a demo project, populating a table, running queries, and seeing the effect of indexing.

1. Create Spring Boot Project

Dependencies (use Spring Initializr):

• Spring Web
• Spring Data JPA
• MySQL Driver
• Lombok (optional, for boilerplate code)

2. Configure application.properties

1. Open the application.properties file and configure your database settings. Here’s an example configuration:

2. Replace your_database_name, your_username, and your_password with your database details.

# Server configuration
server.port = 8081
          
# Database configuration
spring.datasource.url=jdbc:mysql://localhost:3306/your_database_name
spring.datasource.name=your_database_name
spring.datasource.username=your_username
spring.datasource.password=your_password
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
          
# JPA configuration
spring.jpa.hibernate.ddl-auto=update
        

For More Details:

If you want to learn more about configuring the application.properties file, check out this detailed article:

Create REST APIs Using Spring Boot: Step-by-Step Guide

Create Entity Class

@Entity
@Table(name = "employee")
@Data
@NoArgsConstructor
@AllArgsConstructor
public class Employee {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private String firstName;
    private String lastName;
    private String email;
    private String department;
}

Create Repository Interface

package com.example.indexdemo.repository;

import com.example.indexdemo.entity.Employee;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface EmployeeRepository 
        extends JpaRepository<Employee, Long> {

    List<Employee> findByLastName(String lastName);
}

Create Service Class

package com.example.indexdemo.service.impl;

import com.example.indexdemo.entity.Employee;
import com.example.indexdemo.repository.EmployeeRepository;
import com.example.indexdemo.service.EmployeeService;
import org.springframework.stereotype.Service;

import java.util.List;

@Service
public class EmployeeServiceImpl implements EmployeeService {

    private final EmployeeRepository employeeRepository;

    public EmployeeServiceImpl(EmployeeRepository employeeRepository) {
        // Constructor injection
        this.employeeRepository = employeeRepository;
    }

    @Override
    public List<Employee> getAllEmployees() {
        return employeeRepository.findAll();
    }

    @Override
    public List<Employee> getByLastName(String lastName) {
        return employeeRepository.findByLastName(lastName);
    }
}

Create REST Controller

package com.example.indexdemo.controller;

import com.example.indexdemo.entity.Employee;
import com.example.indexdemo.service.EmployeeService;
import org.springframework.web.bind.annotation.*;

import java.util.List;

@RestController
@RequestMapping("/employees")
public class EmployeeController {

    private final EmployeeService employeeService;

    public EmployeeController(EmployeeService employeeService) {
        // Constructor injection
        this.employeeService = employeeService;
    }

    // Get all employees
    @GetMapping
    public List<Employee> getAllEmployees() {
        return employeeService.getAllEmployees();
    }

    // Search by last name
    @GetMapping("/search")
    public List<Employee> getByLastName(@RequestParam String lastName) {
        return employeeService.getByLastName(lastName);
    }
}

Run Spring Boot Application

Run your spring boot application and it will automatically create the employee table in your database.

Now for understanding indexing, we need insert some employee data in table. You can run following script to insert thousands of records into the employee table.

-- Insert sample data ( inserting 1k rows for testing)
DELIMITER $$
CREATE PROCEDURE insert_dummy_data()
BEGIN
    DECLARE i INT DEFAULT 1;
    WHILE i <= 1000 DO
        INSERT INTO employee (first_name, last_name, email, department)
        VALUES (
            CONCAT('First', i), 
            CONCAT('Last', i), 
            CONCAT('user', i, '@example.com'), 
            'IT'
        );
        SET i = i + 1;
    END WHILE;
END$$
DELIMITER ;

CALL insert_dummy_data();

After running above script you check the employee table data there should be 1000 record that we have generated using script

Now let’s test our api performance using postman by searching employee by last name without index

When you hit this then its internally runs following query to fetch the data by last name.

EXPLAIN SELECT * 
FROM employee 
WHERE last_name = 'Last500';

The above query will scan the entire table to find the matching last name. It will check all the rows in the table, which is a very slow process, especially when dealing with large amounts of data.

Now Create Index on last_name column in employee table

You can create an index in two ways – using SQL Query or using JPA annotation. Here, I will show you both ways.

1. Add Indexing using SQL Query:

CREATE INDEX idx_last_name ON employee(last_name);

2. You can tell JPA/Hibernate to create indexes directly from your entity class by using the @Table annotation’s indexes property.:

Here’s how you can add indexing to your Employee entity:

import jakarta.persistence.*;
import lombok.*;

@Entity
@Table(
    name = "employee",
    indexes = {
        @Index(name = "idx_last_name", columnList = "lastName"),
        @Index(name = "idx_email", columnList = "email", unique = true) // example: unique index
    }
)
@Data
@NoArgsConstructor
@AllArgsConstructor
public class Employee {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private String firstName;

    private String lastName;

    private String email;

    private String department;
}

🔹 Explanation

1. indexes in @Table

• @Index(name = "idx_last_name", columnList = "lastName") → creates a normal index on lastName.
• @Index(name = "idx_email", columnList = "email", unique = true) → creates a unique index, ensuring no duplicate emails.

2. columnList

• Can have one or multiple columns (for composite indexes).

Example

@Index(name = "idx_name", columnList = "firstName,lastName")

When to Use Indexing in Database?

• lastName → normal index (faster searches, not unique).
• email → unique index (faster lookups + ensures data integrity).
• Composite (firstName, lastName) → when you often search by both together.

👉 With this, your Spring Boot app will automatically create indexes when Hibernate generates/updates the schema.

Now let’s test our api performance again by searching employee by last name with index

when you hit this then its internally run the query to fetch the data by last name. but in this time it will use the index to fetch the data. don’t check every row in the table that will improve the performance of the query.

Summary

• Without an index → full table scan → slower.
• With an index → uses indexed column → faster lookup.
• Spring Boot + JPA allows you to query indexed columns efficiently without writing raw SQL.

Conclusion

✅ This application demonstrates how indexing improves database query performance. You can extend it by creating composite indexes or unique indexes.