MySql - Creating indexes

Creating Indexes (CREATE INDEX) in MySQL

Introduction to Indexes in MySQL

Indexes are a powerful feature in MySQL that improve the speed of data retrieval operations on a database table. Just like the index of a book helps you quickly find topics, a database index allows MySQL to find rows faster based on column values. Indexes are especially useful on large tables with millions of rows where performance becomes critical.

This guide covers the fundamentals of creating indexes in MySQL using the CREATE INDEX statement, types of indexes, syntax, use cases, and best practices.

Why Are Indexes Important?

Indexes are essential for optimizing query performance. Without indexes, MySQL must scan the entire table (a full table scan) to find matching rows. This is inefficient and can lead to significant slowdowns in large databases. By using indexes, MySQL can use efficient search algorithms such as binary search or B-tree traversals to locate the required data.

How Indexes Work

When an index is created on a table column, MySQL builds a data structure (usually a B-tree or hash) that stores the column values along with pointers to the corresponding table rows. When a query is run, MySQL checks whether it can use an index to satisfy the condition. If so, it uses the index to jump directly to the matching rows.

Syntax of CREATE INDEX

Basic Syntax

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

Parameters

• UNIQUE - Ensures all values in the index are unique.
• FULLTEXT - For full-text searching (only on CHAR, VARCHAR, TEXT types).
• SPATIAL - For spatial data types (used with geometry data types).
• index_name - A unique name for the index.
• table_name - The name of the table on which to create the index.
• column1, column2 - One or more columns to include in the index.

Example

CREATE INDEX idx_lastname
ON employees (last_name);

Creating Indexes Using ALTER TABLE

Alternatively, indexes can also be created using the ALTER TABLE statement:

ALTER TABLE employees
ADD INDEX idx_lastname (last_name);

Types of Indexes in MySQL

1. Single-Column Index

An index on a single column. Used when queries filter or sort based on one column.

CREATE INDEX idx_email ON users (email);

2. Composite Index (Multi-Column Index)

Indexes that include multiple columns. They can be used when a WHERE clause or ORDER BY references multiple columns.

CREATE INDEX idx_name_dob ON users (last_name, date_of_birth);

Note: The order of columns in a composite index matters!

3. UNIQUE Index

Ensures all values in the index column(s) are unique.

CREATE UNIQUE INDEX idx_username ON users (username);

4. FULLTEXT Index

Used for full-text search operations on textual data (MyISAM, InnoDB since MySQL 5.6+).

CREATE FULLTEXT INDEX idx_description ON products (description);

5. SPATIAL Index

Used for spatial data types like POINT, LINESTRING, POLYGON. Only works with MyISAM and InnoDB tables in some cases.

CREATE SPATIAL INDEX idx_location ON places (location);

How to View Indexes

You can view existing indexes on a table using:

SHOW INDEX FROM table_name;

How MySQL Uses Indexes

1. WHERE Clauses

Indexes help filter rows faster in SELECT statements:

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

2. JOIN Clauses

Indexes on foreign key columns improve join performance:

SELECT orders.order_id, customers.name
FROM orders
JOIN customers ON orders.customer_id = customers.customer_id;

3. ORDER BY Clauses

Indexes can optimize ORDER BY if it matches the indexed columns.

SELECT * FROM users ORDER BY last_name;

4. GROUP BY Clauses

Similar to ORDER BY, indexes help group rows more efficiently.

SELECT department_id, COUNT(*) FROM employees GROUP BY department_id;

Index Prefix Length

When creating indexes on large text fields (VARCHAR, TEXT), MySQL allows specifying a prefix length:

CREATE INDEX idx_email_prefix ON users (email(10));

This tells MySQL to only index the first 10 characters of the email field.

Indexes are essential for building performant and scalable MySQL applications. They help reduce query execution time and enable efficient data access. However, indexing should be done thoughtfully—over-indexing can hurt performance, increase storage costs, and complicate maintenance. Understanding when and how to use the CREATE INDEX  command effectively can have a major impact on your database efficiency.