TL;DR: The Quick Fix
MySQL refuses to index the entire contents of a TEXT or BLOB column because these fields can hold massive amounts of data. To fix Error 1170, you must define a prefix length—a specific number of characters for MySQL to track.
Instead of this failing command:
CREATE INDEX idx_description ON products(description);
Use this syntax to index only the first 191 characters:
CREATE INDEX idx_description ON products(description(191));
Why Does This Error Happen?
Storage engines like InnoDB have strict limits on index key lengths. While a LONGTEXT column can store 4GB of data, InnoDB’s internal index limit is often 767 bytes (for older formats) or 3072 bytes (for modern DYNAMIC formats). If MySQL tried to index a full 4GB field, your database performance would plummet and the index files would explode in size.
By requiring a prefix length, MySQL ensures your index remains lean. You’re essentially telling the database: "Just look at the start of this string to figure out where the data is." This keeps lookups fast without hitting hardware bottlenecks.
Three Ways to Resolve Error 1170
1. Implement a Prefix Index
Most developers solve this by choosing a length that balances uniqueness with performance. A common choice is 191 or 255 characters.
Using ALTER TABLE:
ALTER TABLE articles ADD INDEX (content(255));
Using CREATE INDEX:
CREATE INDEX idx_title_prefix ON blog_posts (title(100));
Technical Note: If you use the utf8mb4 character set, MySQL reserves 4 bytes per character. A prefix of 191 characters equals 764 bytes (191 * 4), which safely fits under the classic 767-byte limit found in older MySQL configurations.
2. Convert the Column to VARCHAR
Do you actually need a TEXT column? If your data usually fits in 255 or 500 characters, VARCHAR is a better choice. Unlike TEXT, VARCHAR columns don't require a prefix length for indexing as long as the total size stays within engine limits.
-- Switch from TEXT to VARCHAR(255)
ALTER TABLE users MODIFY COLUMN bio VARCHAR(255);
CREATE INDEX idx_bio ON users(bio);
3. Use FULLTEXT for Heavy Searches
Standard B-tree indexes are great for exact matches, but they struggle with "contains" searches. If you're building a search bar for blog posts, a prefix index won't help much. Switch to a FULLTEXT index instead; it doesn't require a prefix length and handles large blocks of text efficiently.
ALTER TABLE products ADD FULLTEXT(description);
Keep in mind that FULLTEXT indexes require specific syntax. You'll need to use MATCH() ... AGAINST() in your SQL queries rather than standard WHERE clauses.
Finding the "Sweet Spot" for Prefix Length
Don't guess your index length. You can use math to find the most efficient number. Your goal is to find a length where the prefixes are almost as unique as the full strings.
Compare these counts to see where the uniqueness levels off:
-- 1. Total unique values in the table
SELECT COUNT(DISTINCT description) FROM products;
-- 2. Unique values if we only look at the first 10 characters
SELECT COUNT(DISTINCT LEFT(description, 10)) FROM products;
-- 3. Unique values if we look at the first 20 characters
SELECT COUNT(DISTINCT LEFT(description, 20)) FROM products;
If the count for 20 characters is 99% of the total unique count, then 20 is a perfect, high-performance prefix length.
How to Verify Your Changes
Once you've applied the fix, double-check the index structure. Run the following command:
SHOW INDEX FROM table_name;
Check the Sub_part column. It should display the integer you specified (like 191). If it shows NULL, the index covers the entire column—which only happens for non-BLOB types.
Performance Trade-offs
- Keep it short: Larger prefixes make indexes heavier and slow down
INSERToperations. Only index what you need. - Avoid BLOB Primary Keys: Using a
TEXTcolumn as a Primary Key is an architectural red flag. Stick toBIGINT AUTO_INCREMENTorUUIDfor better performance. - Sorting issues: MySQL generally cannot use prefix indexes for
ORDER BYorGROUP BYoperations. If you frequently sort by this column, consider a different schema design.