DEV Community

ATHEEBA PARVEEN J A 24CB002
ATHEEBA PARVEEN J A 24CB002

Posted on

"Indexing, Hashing & Query Optimization with MySQL — Students Table Example"

#database #sql #mysql

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
);
Enter fullscreen mode Exit fullscreen mode

CREATE TABLE

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);
Enter fullscreen mode Exit fullscreen mode

INSERT VALUES

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;
Enter fullscreen mode Exit fullscreen mode

Step 4: Query Student by Roll Number

Fetch details of the student with roll_no = 110:

SELECT * 
FROM Students
WHERE roll_no = 110;
Enter fullscreen mode Exit fullscreen mode

FETCH DETAILS

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;
Enter fullscreen mode Exit fullscreen mode

Step 6: Query Students with CGPA > 8.0 

SELECT *
FROM Students
WHERE cgpa > 8.0
ORDER BY cgpa DESC;
Enter fullscreen mode Exit fullscreen mode

FETCH DETAILS

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;
Enter fullscreen mode Exit fullscreen mode

Step 8: Query implement by Department 

SELECT *
FROM Students
WHERE dept = 'CSBS';
Enter fullscreen mode Exit fullscreen mode

FETCH DETAILS

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)