How can partial indexes or prefix indexes be used for large text columns?

Using Partial (Prefix) Indexes for Large Text Columns in MySQL

Large text columns such as VARCHAR(1000), TEXT, or LONGTEXT cannot be fully indexed efficiently. MySQL allows prefix indexes, where only the first N characters of a column are indexed, reducing index size while still improving search performance.

What Is a Prefix (Partial) Index?

1
A prefix index stores only the first part of a string column (e.g., first 50 characters).
2
It reduces index size and memory usage.
3
Helps speed up lookups on large text columns without indexing the entire string.

Example: Creating a Prefix Index

Here, only the first 50 characters of title are indexed. MySQL can use this index for equality, prefix matching, and some range queries.

When Prefix Indexes Are Useful

1
Indexing large VARCHAR or TEXT columns.
2
Improving performance without huge index storage.
3
When the first few characters of the text are usually unique enough.
4
For email fields, URLs, descriptions, product names, etc.

Choosing the Right Prefix Length

1
Too short → poor selectivity, index becomes ineffective.
2
Too long → index becomes large and defeats the purpose.
3
Use SHOW INDEX or SELECT COUNT(DISTINCT LEFT(col, N)) to determine optimal length.

Finding a Good Prefix Length

Limitations of Prefix Indexes

1
Cannot be used for ORDER BY without full-column sorting.
2
Cannot be used in FULLTEXT searches.
3
Cannot cover a query unless full column fits inside the index.
4
Not usable for unique constraints unless prefix guarantees uniqueness.

Alternative: FULLTEXT Indexes

1
Use when searching inside long text content.
2
Best for natural language queries and relevance ranking.
3
Prefix indexes are not a replacement for full-text search.

Prefix indexes provide a powerful way to index large text columns efficiently. They significantly reduce disk and memory usage while giving MySQL enough information to optimize searches on long string fields.

Difficulty: 6/10

Topics: partial indexes, prefix indexes, large text columns

Scenario Questions

0-2 years experience

1
We have a comments table with a TEXT column body. How would you create an index to speed up queries that look for rows where body starts with a given prefix?
2
If you add a prefix index on the first 100 characters of a VARCHAR(5000) column, which types of WHERE clauses can use that index and which cannot?
3
What happens if you try to create a regular B‑Tree index on a LONGTEXT column without specifying a prefix length?

2-5 years experience

1
Our feature needs to find all articles where the content column (MEDIUMTEXT) contains a specific keyword, but a full‑text index is too heavy. How would you use a partial index to improve performance, and what are the trade‑offs?
2
A recent deployment made a query that filters on email (VARCHAR(255)) much slower. The index was defined as INDEX(email(10)). Explain why the optimizer might ignore that index and how you would fix the issue.
3
During a load test, a query using WHERE title LIKE 'My%...' runs slowly even though you have a prefix index on title(255). What could be wrong and how would you debug it?

5-8 years experience

1
Design a migration strategy for a legacy logs table with a LONGTEXT column to start using prefix indexes for recent queries while keeping write throughput high. What schema changes, index maintenance, and monitoring would you implement?
2
Our system stores user‑generated JSON blobs in a TEXT column and we need to filter rows where the JSON starts with a certain key. Discuss the pros and cons of using a prefix index versus a generated virtual column with a normal index.
3
Explain how MySQL’s limit on prefix length (e.g., 3072 bytes for InnoDB) impacts indexing very large VARCHAR columns, and how you would decide the optimal prefix length in a high‑traffic environment.

8+ years experience

1
We are building a multi‑tenant SaaS platform where each tenant can store documents up to 10 MB in a MySQL MEDIUMBLOB. How would you architect the indexing approach across all tenants, considering prefix indexes, sharding, and future schema evolution?
2
A cross‑team initiative wants to replace several full‑text indexes with prefix indexes to cut storage costs. What are the long‑term maintenance, compatibility, and query‑ability implications, and how would you guide the rollout?
3
Describe how you would set up automated testing and alerting to detect when a prefix index becomes ineffective due to data growth or changed query patterns in a critical service.

Follow-up Questions

❓What factors would you consider when choosing the prefix length?
❓How does the storage size of a prefix index compare to a full index on the same column?
❓Can a prefix index be used for equality comparisons on the full column value? Why or why not?