MySQL CREATE INDEX: Syntax, Examples & Tips

MySQL CREATE INDEX adds an index to an existing table to speed up SELECT queries, JOINs, and WHERE clauses. You specify the index name, the table, and one or more columns; MySQL then builds a sorted data structure that lets the database find rows without scanning the whole table. This article explains the syntax, how indexes work, how to choose columns, common mistakes, and performance trade-offs.

Why Indexes Matter

Without an index, MySQL performs a full table scan: it reads every row to find matches. On a table with millions of rows, that is slow. An index is like the index at the back of a book—it tells the database where to look. When you run a query with a WHERE clause on an indexed column, MySQL can jump directly to the relevant rows instead of reading everything.

Indexes also speed up ORDER BY, GROUP BY, and joins between tables. However, they are not free: every index adds storage overhead and slows down INSERT, UPDATE, and DELETE because the index must be updated whenever data changes. The key is to index what your queries actually use.

CREATE INDEX Syntax

The basic syntax is:

CREATE INDEX index_name ON table_name (column1, column2, ...);

You can also create a unique index to enforce uniqueness:

CREATE UNIQUE INDEX index_name ON table_name (column);

And a fulltext index for text search:

CREATE FULLTEXT INDEX index_name ON table_name (column);

To create an index when you first create the table, use the INDEX keyword inside the CREATE TABLE statement:

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

The PRIMARY KEY automatically creates a unique index, so you do not need to add one manually for the primary key column.

How MySQL Uses Indexes

When you run a query like SELECT * FROM users WHERE email = 'a@b.com', MySQL checks if there is an index on email. If yes, it uses the index to locate the row(s) quickly. The index stores the column value plus a pointer to the actual row. MySQL can also use an index to avoid sorting: if the index is on the column you are ordering by, the data is already in order.

For composite indexes (multiple columns), the order matters. The leftmost prefix rule says that MySQL can use the index for searches on the first column, the first two columns, and so on, but not if you skip the first column. For example, an index on (last_name, first_name) helps queries filtering by last_name alone, but not by first_name alone.

Choosing Which Columns to Index

Not every column needs an index. Good candidates are:

• Columns used in WHERE clauses frequently
• Columns used in JOIN conditions
• Columns used in ORDER BY or GROUP BY
• Columns with high selectivity (many distinct values, like email or user_id)

Avoid indexing columns that are rarely queried, have very few distinct values (like a boolean flag), or are large text fields (unless using fulltext). For a composite index, put the most selective column first, and order columns to match your most common query patterns.

Use EXPLAIN SELECT ... to see whether MySQL actually uses an index for a given query. The output shows which index is used and how many rows are examined. If an index is not being used, it may be because the query pattern does not match the index order, or the optimizer decides a full scan is cheaper for a small table.

Common Mistakes and Pitfalls

• Indexing too many columns: Each index slows down writes and uses disk space. Only add what your queries need.
• Wrong column order in composite indexes: The leftmost prefix rule means the first column must be used in the query for the index to help.
• Using LIKE '%text' on an indexed column: A leading wildcard prevents index usage. Use LIKE 'text%' instead.
• Indexing a column with functions: WHERE YEAR(date_col) = 2024 will not use an index on date_col unless you create a functional index (MySQL 8.0+ supports CREATE INDEX ... ( (expression) )).
• Forgetting that NULL values are indexed: MySQL can use an index for IS NULL queries, but be aware that unique indexes allow multiple NULL values.

Index Types and Storage Engines

MySQL supports several index types depending on the storage engine:

• BTREE – the default for InnoDB and MyISAM. Good for equality and range queries.
• HASH – used by MEMORY tables. Fast for exact matches, but not for ranges.
• FULLTEXT – for full-text search on text columns. Available in InnoDB and MyISAM.
• SPATIAL – for geographic data in MyISAM and InnoDB (with the right data types).

InnoDB tables use a clustered index on the primary key. That means the actual data rows are stored in the order of the primary key. Secondary indexes (non-primary) store the primary key value as the pointer, so a lookup using a secondary index requires two steps: first find the primary key in the index, then look up the row by primary key.

Performance Costs and Maintenance

Adding an index to a large table can take time and lock the table (depending on the MySQL version and ALGORITHM). In MySQL 8.0, you can use ALGORITHM=INPLACE to avoid a full copy for many operations. The DROP INDEX command removes an index:

DROP INDEX index_name ON table_name;

Indexes also consume disk space. A large composite index on a big table can be several gigabytes. Monitor your disk usage and remove unused indexes. Use the SHOW INDEX FROM table_name command to list existing indexes, and check the performance_schema or sys schema for unused index statistics.

Alternatives to CREATE INDEX

Sometimes you can avoid an index by rewriting the query. For example, using a covering index (an index that contains all columns needed by the query) lets MySQL read the index alone without touching the table. You can also use generated columns to precompute values and index those. In MySQL 8.0, functional indexes allow indexing expressions directly. For very large datasets, partitioning can help, but that is a separate feature and not a replacement for indexing.

Frequently Asked Questions

Can I create an index on multiple columns?

Yes, use CREATE INDEX idx_name ON table (col1, col2, col3). This is called a composite index. It helps queries that use the columns in order from left to right.

Does CREATE INDEX lock the table?

In MySQL 8.0, many index creation operations use ALGORITHM=INPLACE and do not block reads or writes, but there can be brief metadata locks. On older versions, it may lock the table. For very large tables, consider using a tool like pt-online-schema-change to avoid downtime.

What is the difference between INDEX and UNIQUE INDEX?

A regular index allows duplicate values. A unique index ensures that no two rows have the same value (or combination of values) in the indexed columns. Unique indexes also speed up queries, but they add an extra check on every write.

How do I know if my index is being used?

Run EXPLAIN SELECT ... and look at the key column. If it shows your index name, it is being used. If it shows NULL, no index is used. Also check the rows column to see how many rows were examined.

Can I create an index on a text column?

Yes, but with a length limit. For TEXT columns, you must specify a prefix length: CREATE INDEX idx_text ON table (text_col(100)). For full-text search, use a FULLTEXT index instead.

What happens if I drop an index?

Queries that relied on that index will become slower, but the data is unaffected. Writes become faster because there is one less index to update. You can recreate the index later with CREATE INDEX.

Related

• Twitch 2FA Code Not Received? Fix It Now
• Cloud Automation for Business Operations: Do You Need It?
• Razer Tomahawk Modular Gaming Desktop: CES Unveil