What Is a Prefix Indexes in MySQL?
A prefix index stores only the first N characters or bytes of a string. It can narrow prefix-compatible searches with a smaller key.
How the Prefix Indexes Algorithm Works
A prefix index stores only the first N characters or bytes of a string. It can narrow prefix-compatible searches with a smaller key.
When to Use Prefix Indexes
Long string fields whose leading characters are selective.
How to Create a MySQL Prefix Indexes
Review data quality, existing structures, feature support, build time, and deployment requirements before creating an index on a production table.
CREATE INDEX users_email_prefix_idx ON users (email(24));Prefix Indexes Query Example
This example demonstrates the matching query shape. Check the execution plan to confirm that the engine can use the index.
SELECT id FROM users WHERE email LIKE 'reader@%';Queries Supported by Prefix Indexes
Long string fields whose leading characters are selective. A similar-looking predicate may not use the same access path; operators, expressions, key order, and data type all matter.
Read Performance, Storage, and Write Costs
Many shared prefixes cause extra row checks; it may not cover the entire original value. Indexes reduce some search work but require storage and add maintenance to writes.
Prefix Indexes Limitations and Common Mistakes
Prefix length and byte/character semantics depend on charset; unique prefix keys enforce only prefix uniqueness.
Do not assume that an index being present means the optimizer chose it or that the query became selective.
How to Measure and Tune Prefix Indexes
Measure prefix cardinality and EXPLAIN rows; choose the shortest prefix that is selective.
Compare representative plans and timings before and after, including concurrent writes and realistic data volumes.
Prefix Indexes Questions
Will the database always use this index?
No. The optimizer weighs selectivity, estimated costs, statistics, cache state, and the amount of data returned.
What should I verify before production?
Confirm version support, query compatibility, write overhead, build/rollback procedures, and index behavior during backup and recovery.
Official MySQL Index Documentation
Check MySQL 8.4 Reference Manual: Optimization and Indexes for release-specific requirements and supported operators.