Introduction
To start with the definations,well indexing is a data structure technique used to improve the speed of data retrieval operations in a database,hashing is used to map data to a fixed-size value (ie hash) using a hash function and Query Optimization is the process of improving the efficiency of SQL queries so that they run faster and consume fewer resources (CPU, memory, disk I/O).
When working with large datasets, performance becomes critical.
In this blog post, we will explore different types of indexes in SQL using a simple Students table:
1.B-Tree Index
2.B+ Tree Index
3.Hash Index
We will walk through creating indexes and using queries to demonstrate their impact.
Firstly we will create a table called students as shown below:
CREATE TABLE Students (
roll_no INT PRIMARY KEY,
name VARCHAR(50),
dept VARCHAR(10),
cgpa DECIMAL(3,2)
);
Inserting at least 20 sample records
`INSERT INTO Students (roll_no, name, dept, cgpa) VALUES
(101, 'Alice', 'CSBS', 8.5);
INSERT INTO Students (roll_no, name, dept, cgpa) VALUES
(102, 'Bob', 'MECH', 7.2);
INSERT INTO Students (roll_no, name, dept, cgpa) VALUES
(103, 'Charlie', 'EE', 9.1);
INSERT INTO Students (roll_no, name, dept, cgpa) VALUES
(104, 'David', 'CSBS', 6.8);
INSERT INTO Students (roll_no, name, dept, cgpa) VALUES
(105, 'Eva', 'MECH', 8.3);
INSERT INTO Students (roll_no, name, dept, cgpa) VALUES
(106, 'Frank', 'EE', 7.9);
INSERT INTO Students (roll_no, name, dept, cgpa) VALUES
(107, 'Grace', 'CSBS', 9.0);
INSERT INTO Students (roll_no, name, dept, cgpa) VALUES
(108, 'Helen', 'MECH', 8.6);
INSERT INTO Students (roll_no, name, dept, cgpa) VALUES
(109, 'Ian', 'EE', 7.5);
INSERT INTO Students (roll_no, name, dept, cgpa) VALUES
(110, 'Jack', 'CSBS', 8.9);
INSERT INTO Students (roll_no, name, dept, cgpa) VALUES
(111, 'Karen', 'MECH', 7.7);
INSERT INTO Students (roll_no, name, dept, cgpa) VALUES
(112, 'Leo', 'EE', 8.2);
INSERT INTO Students (roll_no, name, dept, cgpa) VALUES
(113, 'Mona', 'CSBS', 9.3);
INSERT INTO Students (roll_no, name, dept, cgpa) VALUES
(114, 'Nate', 'MECH', 6.9);
INSERT INTO Students (roll_no, name, dept, cgpa) VALUES
(115, 'Olivia', 'EE', 8.0);
INSERT INTO Students (roll_no, name, dept, cgpa) VALUES
(116, 'Paul', 'CSBS', 8.4);
INSERT INTO Students (roll_no, name, dept, cgpa) VALUES
(117, 'Quinn', 'MECH', 7.6);
INSERT INTO Students (roll_no, name, dept, cgpa) VALUES
(118, 'Rita', 'EE', 9.4);
INSERT INTO Students (roll_no, name, dept, cgpa) VALUES
(119, 'Sam', 'CSBS', 8.1);
INSERT INTO Students (roll_no, name, dept, cgpa) VALUES
(120, 'Tina', 'MECH', 7.8);
`
Create a B-Tree index on the roll_no column of the Students table and Execute a query to fetch the details of a student with roll_no = 116.
CREATE INDEX idx_roll_no ON Students (roll_no);
SELECT * FROM Students WHERE roll_no = 110;
Create a B+ Tree index on the cgpa column of the Students table.Write a query to display all students with cgpa > 8.0.
CREATE INDEX idx_cgpa ON Students (cgpa);
SELECT * FROM Students WHERE cgpa > 8.0;
Create a Hash index on the dept column of the Students table and Run a query to retrieve all students from the 'CSBS' department.
CREATE INDEX idx_dept_hash ON Students USING HASH (dept);
SELECT * FROM Students WHERE dept = 'CSBS';
CONCLUSION
Indexing is a cornerstone of database performance optimization. By choosing the right type of index you tend to speed up data retrieval and improve your application's responsiveness.
Mastering indexing not only enhances performance but also helps maintain scalable and efficient databases as your data grows.Thanks to @santhoshnc for the guidance.
Top comments (0)