In one of my project, we were facing slower responses from the microservice, though the benchmark target was within 10ms, due to large datasets, but ours was taking more than 30s to complete. This made us wonder on what is messing and finding the root cause of it.
Like we all do, we directly jumped to JProfiler and found out that the database queries were taking a lot of time.
The ideal understanding was that the DB queries are optimized and we need not worry, but that is where we found the issue.
On debugging further we realized the select queries that we were firing had columns which were not optimized and that was the sole reason for the performance to go down.
The solution then was to create a custom index, one of the most powerful techniques to optimize database performance.
What is an Index in SQL?
An index is like a book index. Instead of flipping through every page to find a topic, you can jump directly to the right page number. Similarly, a database index allows the query engine to jump directly to the row location instead of scanning the entire table.
Benefits of Indexing in SELECT Queries
1. Faster Lookups – Quickly find rows with WHERE conditions.
2. Efficient Range Queries – Speeds up queries like BETWEEN, <, >.
3. Improved Sorting – Helps with ORDER BY clauses.
4. Faster Joins – Joins on indexed columns run significantly faster.
Real-Time Example with 1 Million Rows
Step 1: Create a Sample Table
CREATE TABLE users (
id SERIAL PRIMARY KEY,
name VARCHAR(100),
email VARCHAR(255)
);
Insert sample data (1M rows):
INSERT INTO users (name, email)
SELECT
'User' || i,
'user' || i || '@test.com'
FROM generate_series(1, 1000000) AS i;
---
Step 2: Run SELECT Without Index
EXPLAIN ANALYZE
SELECT * FROM users WHERE email = 'user999999@test.com';
Output (simplified):
Seq Scan on users (cost=0.00..20000.00 rows=1 width=64)
Execution Time: ~150 ms
The database performed a sequential scan over all 1 million rows.
Step 3: Create an Index
CREATE INDEX idx_users_email ON users(email);
Step 4: Run SELECT With Index
EXPLAIN ANALYZE
SELECT * FROM users WHERE email = 'user999999@test.com';
Output:
Index Scan using idx_users_email on users
Execution Time: ~0.2 ms
Performance improved drastically — from 150 ms to just 0.2 ms.
Step 5: Range Query Example
EXPLAIN ANALYZE
SELECT * FROM users
WHERE email BETWEEN 'user1000@test.com' AND 'user2000@test.com';
Again, the index is used for efficient range scanning.
Why an Index May Not Be Used
Low Selectivity: If most rows match (e.g., gender = 'M' in a billion-row table).
Functions on Columns:
WHERE LOWER(email) = 'user@test.com';
(Solution: create a functional index).
Composite Index Rule: The leading column must be in the query.
Small Tables: DB may choose a full scan as it’s faster.
Best Practices
Index columns frequently used in WHERE, JOIN, and ORDER BY.
Avoid too many indexes — they slow down INSERT/UPDATE/DELETE.
Always use EXPLAIN or EXPLAIN ANALYZE to confirm index usage.
Use composite indexes wisely (column order matters).
Conclusion
Indexing can turn a query that scans 1 million rows into a lightning-fast lookup. Always analyze your queries, add indexes strategically, and verify their usage with EXPLAIN. Proper indexing is the key to scaling database performance.
0 Comments