9️⃣ MySQL Indexing – Speed Up Queries with Primary, Unique & Composite Indexes

Indexing in MySQL is like adding a high-speed search engine to your tables. Without indexes, the database must scan every row to find matching records—causing serious slowdowns as your data grows.

Whether you’re building a CRM, eCommerce platform, or CMS, indexing is key to performance, especially for SELECT, JOIN, and WHERE operations.

Introduction – Why Use Indexing in MySQL?

Indexes improve query performance by avoiding full-table scans. With properly chosen indexes, MySQL can jump directly to relevant rows.

Boosts SELECT speed
Improves JOIN efficiency
Speeds up sorting and filtering
Reduces CPU and memory usage on large datasets

Topics Covered

What Is an Index?

An index is a data structure (typically B-tree or hash) that helps MySQL quickly locate rows based on values in indexed columns.

Think of it like a book’s index—jumping to exact topics instead of flipping every page.

Types of Indexes in MySQL

1. Creating Indexes

Add Index on Table Creation

CREATE TABLE users (
  id INT PRIMARY KEY,
  email VARCHAR(100),
  INDEX (email)
);

Add Index on Existing Table

CREATE INDEX idx_email ON users(email);

Unique Index

CREATE UNIQUE INDEX idx_username ON users(username);

Prevents duplicates and accelerates WHERE username = ? queries.

Composite Index

CREATE INDEX idx_name_dept ON employees(last_name, department);

Best for:

SELECT * FROM employees WHERE last_name = 'Smith' AND department = 'HR';

Prefix Index (TEXT/VARCHAR)

CREATE INDEX idx_title ON articles(title(100));

Useful when full string indexing is inefficient or unsupported.

2. Viewing Indexes

Use the SHOW INDEXES command:

SHOW INDEXES FROM users;

Returns:

• Index name
• Column(s) indexed
• Uniqueness
• Cardinality (selectivity)

3. Dropping Indexes

Remove an index from a table:

DROP INDEX idx_email ON users;

4. Query Optimization with Indexes

Use the EXPLAIN command:

EXPLAIN SELECT * FROM users WHERE email = 'user@example.com';

It shows whether an index is used (key field), and how many rows are scanned (rows field).

5. Indexing Best Practices

Real-World Use Cases

Index Pitfalls & Anti-Patterns

Summary – Recap & Next Steps

MySQL indexing is one of the most important performance tools in a developer’s toolkit. By carefully planning indexes, you’ll speed up queries, reduce server load, and build scalable applications.

Key Takeaways

• Use INDEX for filtering, joining, and sorting columns.
• Choose between PRIMARY, UNIQUE, and COMPOSITE based on needs.
• Validate with EXPLAIN to confirm index usage.
• Avoid indexing too many or rarely filtered columns.

Real-World Relevance
Used in search engines, analytics platforms, e-commerce databases, and real-time reporting—indexing is essential for performance at scale.

FAQ – MySQL Indexing

Does indexing improve INSERT/UPDATE speed?

No. Indexes slightly slow down write operations due to maintenance overhead.

Can I index a TEXT or VARCHAR column?

Yes, with a prefix index:

CREATE INDEX idx_bio ON users(bio(255));

Difference between UNIQUE and INDEX?

• UNIQUE: Prevents duplicate values.
• INDEX: Boosts search but allows duplicates.

How to check if a query uses an index?

Use:

EXPLAIN SELECT ...

Look for values in key, possible_keys, and rows.

How many indexes can MySQL support?

Up to 64 indexes per table, including PRIMARY and UNIQUE.