• +91-9142290450

  • Hajipur, Bihar, 844101

☰
❮
❯

MySQL Create Index

← Previous Next →

In MySQL, an index is a database structure that improves the speed of data retrieval from a table. Indexes are essential for optimizing query performance, especially on large tables where searches, filters, and joins can become slow.

The CREATE INDEX statement allows you to create an index on one or more columns of a table to quickly locate rows without scanning the entire table.

What Is an Index in MySQL?

An index is similar to an index in a book—it helps you find information faster.

Key Points:

  • Indexes improve SELECT query performance.

  • They can be applied to one or multiple columns.

  • Do not improve INSERT, UPDATE, or DELETE performance; in fact, indexes slightly slow these operations.

  • Common types of indexes: PRIMARY KEY, UNIQUE, INDEX, and FULLTEXT.

Why Use CREATE INDEX?

  1. Faster Query Execution: Especially for large tables.

  2. Efficient Searching: Quickly find rows matching certain conditions.

  3. Sorting Optimization: Helps ORDER BY queries run faster.

  4. Join Optimization: Speeds up JOIN operations.

  5. Enforcing Uniqueness: Unique indexes ensure column values are unique.

Syntax for CREATE INDEX

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
ON table_name (column1, column2, ...);

Explanation:

  • UNIQUE: Ensures values in the indexed column(s) are unique.

  • FULLTEXT: Used for full-text search in large text columns.

  • SPATIAL: Used for spatial data types.

  • index_name: Name of the index (must be unique in the table).

  • table_name: Table where the index will be created.

  • column1, column2: Column(s) included in the index.

Creating a Simple Index

Example: Create an index on the student_name column in a students table:

CREATE INDEX idx_student_name
ON students(student_name);

Explanation:

  • idx_student_name is the name of the index.

  • Queries filtering by student_name will now execute faster.

Creating a Unique Index

A unique index ensures that no two rows have the same value in the indexed column(s):

CREATE UNIQUE INDEX idx_email
ON students(email);

Explanation:

  • Prevents duplicate email addresses in the students table.

  • Acts similarly to a UNIQUE constraint.

Creating a Composite Index

A composite index is an index on multiple columns:

CREATE INDEX idx_student_class
ON students(class, section);

Explanation:

  • Useful for queries filtering by class and section together.

  • Speeds up multi-column searches and joins.

Creating an Index Using ALTER TABLE

You can also create an index on an existing table using ALTER TABLE:

ALTER TABLE students
ADD INDEX idx_student_name (student_name);

Explanation:

  • Adds an index after table creation.

  • Works identically to CREATE INDEX.

When to Use Indexes

  1. Columns Used in WHERE Clauses: Filtering queries benefit from indexes.

  2. Columns Used in JOINs: Speeds up matching between tables.

  3. Columns Used in ORDER BY or GROUP BY: Reduces sorting time.

  4. Primary Key Columns: Automatically indexed.

  5. Columns Frequently Queried: Improves performance for repeated lookups.

Things to Keep in Mind

  • Indexing slows down INSERT, UPDATE, DELETE operations because indexes need to be updated.

  • Too many indexes can reduce overall performance.

  • Indexes consume disk space; consider size when indexing large tables.

  • Use EXPLAIN to analyze queries and check if indexes are being used effectively.

  • Indexing columns with low cardinality (few unique values) may not improve performance.

Practical Examples

Example 1: Simple Index

CREATE INDEX idx_course_name
ON courses(course_name);

  • Speeds up queries filtering courses by course_name.

Example 2: Composite Index for Joins

CREATE INDEX idx_order_customer
ON orders(customer_id, order_date);

  • Optimizes queries joining orders with customers and filtering by date.

Example 3: Unique Index

CREATE UNIQUE INDEX idx_username
ON users(username);

  • Ensures no two users can have the same username.

Best Practices

  1. Index Only Frequently Queried Columns: Avoid unnecessary indexes.

  2. Use Composite Indexes Wisely: Only when queries filter on all included columns.

  3. Monitor Performance: Use EXPLAIN to verify index usage.

  4. Drop Unused Indexes: Improves write performance.

  5. Name Indexes Clearly: Makes maintenance easier, e.g., idx_table_column.

Common Mistakes to Avoid

  1. Over-Indexing: Too many indexes slow down write operations.

  2. Indexing Low-Cardinality Columns: Minimal performance benefit.

  3. Ignoring Updates: Remember indexes are updated on INSERT, UPDATE, DELETE.

  4. Not Using Composite Indexes When Needed: Multi-column queries may remain slow.

  5. Not Using Descriptive Names: Makes troubleshooting and maintenance difficult.

Summary of the Tutorial

The MySQL CREATE INDEX statement is a critical tool for improving query performance and database efficiency:

  • Supports simple, unique, and composite indexes.

  • Optimizes searches, joins, sorting, and grouping.

  • Can be created at table creation or using ALTER TABLE.

  • Must be used wisely to balance read and write performance.

  • Helps ensure databases remain fast, efficient, and scalable.

Proper use of indexes can significantly reduce query execution time and improve the performance of large-scale applications.

Practice Questions

  1. Create an index on the student_name column in a students table.

  2. Create a unique index on the email column in a students table to prevent duplicates.

  3. Create a composite index on the class and section columns in the students table.

  4. Use ALTER TABLE to add an index on the course_name column in a courses table.

  5. Create a unique index on the username column in a users table.

  6. Create a composite index on customer_id and order_date in the orders table.

  7. Insert multiple rows into the students table and observe query performance with and without the student_name index.

  8. Drop an unused index from the students table using DROP INDEX.

  9. Use EXPLAIN to check if queries on the students table are using the student_name index.

  10. Create a full-text index on a description column in a products table to optimize text search.

← Previous Next →

Try a Short Quiz.

coding learning websites codepractice

No quizzes available.

MySQL

online coding class codepractice

Introduction to MySQL

MySQL SQL Basics

MySQL Functions & Operators

MySQL Joins & Unions

MySQL Grouping & Filtering

Database & Table Management

MySQL Constraints

Date & Views

References

codepractice logo
Login
Go Back Top