Day 39: Creating Indexes for Performance in MySQL!
Sarasa Jyothsna Kamireddi
Aspiring Python Developer | Machine Learning Enthusiast | Experienced in Reliability Engineering
Today, let us explore Indexes in MySQL, a crucial technique to speed up queries and improve database performance!
Index
An index is a data structure that improves the speed of data retrieval operations on a table. It works like an index in a book - allowing MySQL to find rows faster than scanning the entire table.
1. Creating a Basic Index
? Speeds up WHERE clause searches
? Improves JOIN performance
Example: Creating an Index on the 'email' Column
CREATE INDEX idx_email ON customers(email);
Helps quickly find a customer based on their email
Example: Creating a Composite Index on Multiple Columns
CREATE INDEX idx_name_dob ON customers(name, date_of_birth);
Useful when searching by both name and date of birth
2. Types of Indexes in MySQL
a) PRIMARY KEY:
? Unique identifier for each row
? Used for unique row identification
b) UNIQUE
? Ensures values are unique across the column
? Used for email, usernames, etc.
c) INDEX
? Speeds up searches
? Used for frequently searched columns
d) FULLTEXT
? Improves text-based searches
? Used for searching large text fields
e) COMPOSITE
? Indexes multiple columns together
? Used when queries filter multiple columns
3. Checking Existing Indexes
Find indexes on a table:
SHOW INDEXES FROM customers;
4. When to Use Indexes?
? Use indexes on frequently searched columns
? Use indexes on columns used in JOINs
? Use indexes for sorting (ORDER BY)
5. When NOT to Use Indexes?
? On small tables – indexing overhead isn't worth it
? On columns with high updates – indexing slows down INSERT/UPDATE operations
? On low-cardinality columns (e.g., "gender")
6. Removing an Index
DROP INDEX idx_email ON customers;
Removes an index when it's no longer needed
Key Takeaways
? Indexes speed up searches but slow down inserts/updates
? Use wisely on frequently queried columns
? Composite indexes help when filtering multiple columns
#100DaysOfCode #MySQL #SQL #Database #Performance #Optimization #Learning #BackendDevelopment