Indexing a 255-character column costs 255 characters per row in the index, and for a lookup that is unique within the first dozen characters most of that is dead weight — and it may exceed the index size limit entirely.
-- how selective is a prefix?
SELECT COUNT(DISTINCT LEFT(email, 12)) / COUNT(DISTINCT email)
FROM customers;
-- 0.9987 → 12 characters is nearly as selective as the whole column
ALTER TABLE customers ADD INDEX idx_email (email(12));The measurement first, always — a prefix that is not selective produces an index that finds a thousand rows and filters them, which is worse than no index at all in a way that is hard to see. A prefix index cannot be used for a covering read or for ORDER BY, since the truncated value is not the value. On utf8mb4 the 767-byte limit is 191 characters, which is why so many schemas use exactly that number.