How to Create Prefix Index in MySQL: Syntax and Examples

In MySQL, you create a prefix index by specifying the column name followed by the number of characters to index in parentheses, like INDEX (column_name(prefix_length)). This helps optimize searches on large text columns by indexing only the first part of the column.

Syntax

The syntax to create a prefix index in MySQL is:

• CREATE INDEX index_name ON table_name (column_name(prefix_length));
• prefix_length is the number of characters from the start of the column to index.
• This is useful for columns with large text where indexing the full column is inefficient.

sql

CREATE INDEX idx_name ON table_name (column_name(prefix_length));

Example

This example creates a prefix index on the first 10 characters of the email column in the users table. It helps speed up queries filtering by email prefix.

sql

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

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

Output

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.02 sec)

Common Pitfalls

Common mistakes when creating prefix indexes include:

• Choosing a prefix length too short, which reduces index effectiveness.
• Choosing a prefix length too long, which wastes space and may not be allowed for large columns.
• Trying to create a prefix index on non-string columns, which is not supported.
• Not considering character set and collation, which affect prefix length limits.

sql

/* Wrong: prefix length too short, may cause many duplicates */
CREATE INDEX idx_email_short ON users (email(2));

/* Right: reasonable prefix length for uniqueness and size */
CREATE INDEX idx_email_reasonable ON users (email(10));

Quick Reference

Concept	Description
Prefix Index	Index on first N characters of a string column
Syntax	INDEX (column_name(prefix_length))
Use Case	Optimize queries on large text columns
Limitations	Only for string columns; prefix length limits depend on charset
Best Practice	Choose prefix length balancing uniqueness and index size

Key Takeaways

Create prefix indexes by specifying the column and prefix length in parentheses.

Prefix indexes speed up searches on large text columns without indexing full data.

Choose prefix length carefully to balance index size and uniqueness.

Prefix indexes only work on string columns like VARCHAR or TEXT.

Check character set limits to avoid errors with prefix length.