INTRODUCTION
In this tutorial, we explore indexing, hashing, and query optimization using a Students
table in MySQL. We will:
- Create a table with sample student data.
- Insert 20 records.
- Create B-Tree and B+ Tree indexes.
- Create a Hash index.
- Run queries to demonstrate index usage.
- Optimize queries using indexing.
Step 1: Create the Students Table
We start by creating the Students
table with the following fields:
-
roll_no
— integer, primary key. -
name
— varchar(100). -
dept
— varchar(20). -
cgpa
— decimal(3,2).
CREATE TABLE Students (
roll_no INT NOT NULL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
dept VARCHAR(20) NOT NULL,
cgpa DECIMAL(3,2) NOT NULL
);
Step 2: Insert Sample Records
We insert 20 sample students:
INSERT INTO Students (roll_no, name, dept, cgpa) VALUES
(101,'Aisha Khan','CSBS',8.50),
(102,'Rahul Sharma','CSE',7.90),
(103,'Fatima Noor','ECE',8.10),
(104,'Vikram Patel','ME',6.80),
(105,'Sneha Iyer','CSE',9.00),
(106,'Arjun Das','CSBS',7.20),
(107,'Meera Nair','EE',8.75),
(108,'Kabir Ali','CSE',8.20),
(109,'Priya Singh','EEE',7.50),
(110,'Rohit Verma','CSBS',8.80),
(111,'Nisha Gupta','CSE',9.10),
(112,'Sanjay Rao','CSBS',6.95),
(113,'Lina Thomas','IT',8.05),
(114,'Tanuj Sinha','CSE',7.85),
(115,'Shaheen Khan','ECE',8.00),
(116,'Isha Kaur','ME',7.65),
(117,'Devansh Mehta','CSE',8.97),
(118,'Riya Bose','CSBS',9.25),
(119,'Aman Shah','EE',6.50),
(120,'Zara Patel','IT',8.30);
Step 3: Create a B-Tree Index on roll_no
A B-Tree index allows efficient searching on the roll_no column.
CREATE INDEX idx_roll_no ON Students (roll_no) USING BTREE;
Step 4: Query Student by Roll Number
Fetch details of the student with roll_no = 110:
SELECT *
FROM Students
WHERE roll_no = 110;
Step 5: Create a B+ Tree Index on cgpa
A B+ Tree index improves performance for range queries like >, <, or BETWEEN.
CREATE INDEX idx_cgpa ON Students (cgpa) USING BTREE;
Step 6: Query Students with CGPA > 8.0
SELECT *
FROM Students
WHERE cgpa > 8.0
ORDER BY cgpa DESC;
Step 7: Create a Hash Index on dept
A Hash index is optimized for equality lookups (=).
CREATE INDEX idx_dept_hash ON Students (dept) USING HASH;
Step 8: Query implement by Department
SELECT *
FROM Students
WHERE dept = 'CSBS';
Conclusion
In this tutorial, we learned how indexing and hashing can significantly improve database performance in MySQL.
Key Takeaways
B-Tree indexes are efficient for equality searches and help speed up queries on primary keys like roll_no.
B+ Tree indexes are ideal for range queries and ordering, such as fetching students with cgpa > 8.0.
Hash indexes provide fast access for exact match queries but are limited to equality lookups.
Using EXPLAIN
helps you analyze query performance and see if your indexes are being used.
Properly designing and applying indexes is crucial for query optimization, especially as datasets grow larger.
By combining B-Tree, B+ Tree, and Hash indexes, you can ensure that your queries run efficiently and your MySQL database remains optimized.
Top comments (0)