MySQL Prefix Index: Index the First N Characters

Long text columns make large indexes. A prefix index indexes only the first N characters of the column. The index is smaller, and still good if the first N characters are nearly unique.

query.sql

SQL

CREATE TABLE articles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    url VARCHAR(500) NOT NULL,
    title VARCHAR(200) NOT NULL
);
INSERT INTO articles (url, title) VALUES
    ('https://example.com/blog/2026/mysql-indexes-explained', 'Indexes explained'),
    ('https://example.com/blog/2026/mysql-joins-explained', 'Joins explained'),
    ('https://example.com/blog/2025/php-pdo-basics', 'PDO basics'),
    ('https://example.com/tutorials/sql/select', 'SELECT tutorial'),
    ('https://example.com/tutorials/sql/where', 'WHERE tutorial');

CREATE INDEX idx_url ON articles (url(30));

url(30) means “index the first 30 characters of url“.

When you need one

A VARCHAR or TEXT column that is too long for a full index. InnoDB’s limit is 3072 bytes per index entry, and a VARCHAR(1000) in utf8mb4 can need 4,000 bytes.
TEXT and BLOB columns always need a prefix length.
To save space on long, mostly distinct text (URLs, long names).

query.sql

SQL

1
2

CREATE TABLE notes (id INT AUTO_INCREMENT PRIMARY KEY, body TEXT NOT NULL);
CREATE INDEX idx_body ON notes (body);

Output

OUTPUT

ERROR 1170 (42000): BLOB/TEXT column 'body' used in key specification without a key length

Choosing the prefix length

The prefix must be long enough that most values are still different. Compare the selectivity of a prefix with the selectivity of the full column:

query.sql

SQL

SELECT COUNT(DISTINCT url) AS full_column,
       COUNT(DISTINCT LEFT(url, 10)) AS prefix_10,
       COUNT(DISTINCT LEFT(url, 25)) AS prefix_25,
       COUNT(DISTINCT LEFT(url, 40)) AS prefix_40,
       COUNT(DISTINCT LEFT(url, 55)) AS prefix_55
FROM articles;

Output

OUTPUT

+-------------+-----------+-----------+-----------+-----------+
| full_column | prefix_10 | prefix_25 | prefix_40 | prefix_55 |
+-------------+-----------+-----------+-----------+-----------+
|           5 |         1 |         2 |         5 |         5 |
+-------------+-----------+-----------+-----------+-----------+
1 row in set

With a prefix of 10 or 25 characters, every URL starts with https://example.com/, so few values are different. The count grows with longer prefixes, and reaches the full 5 distinct values at the point where the paths start to differ. Choose the shortest prefix that gives (nearly) the same number as the full column. On a big real table, look for a prefix whose distinct count is 95 to 99 percent of the full column’s.

The same idea with the ratio:

query.sql

SQL

SELECT ROUND(COUNT(DISTINCT LEFT(url, 40)) / COUNT(DISTINCT url), 2) AS prefix_40_vs_full FROM articles;

Output

OUTPUT

+-------------------+
| prefix_40_vs_full |
+-------------------+
|              1.00 |
+-------------------+
1 row in set

Limits of prefix indexes

They cannot cover a query. MySQL needs the full value to return it, so it must read the row (“no Using index“).
They cannot be used for ORDER BY or GROUP BY on the full column.
Uniqueness is only for the prefix. UNIQUE KEY (url(30)) rejects two URLs that share the first 30 characters, even if they differ later.

query.sql

SQL

1
2
3

CREATE TABLE links (id INT AUTO_INCREMENT PRIMARY KEY, url VARCHAR(500) NOT NULL, UNIQUE KEY uq_url_prefix (url(20)));
INSERT INTO links (url) VALUES ('https://example.com/page-one');
INSERT INTO links (url) VALUES ('https://example.com/page-two');

Output

OUTPUT

ERROR 1062 (23000): Duplicate entry 'https://example.com/' for key 'links.uq_url_prefix'

The two URLs share their first 20 characters, so the unique index rejects the second one.

Alternatives for long text

Need	Better option
Exact lookups on long text (URLs, file paths)	A hash column: `url_hash CHAR(64) AS (SHA2(url, 256)) STORED` with an index on it
Searching words	A `FULLTEXT` index
Prefix searches (`LIKE 'abc%'`)	A prefix index is a good fit
Case-insensitive lookups	The default collation already is

query.sql

SQL

CREATE TABLE pages (
    id INT AUTO_INCREMENT PRIMARY KEY,
    url TEXT NOT NULL,
    url_hash CHAR(64) AS (SHA2(url, 256)) STORED,
    UNIQUE KEY uq_url_hash (url_hash)
);
INSERT INTO pages (url) VALUES ('https://example.com/a/very/long/path'), ('https://example.com/another/very/long/path');
SELECT id FROM pages WHERE url_hash = SHA2('https://example.com/a/very/long/path', 256) AND url = 'https://example.com/a/very/long/path';

Output

OUTPUT

+----+
| id |
+----+
|  1 |
+----+
1 row in set

The hash is short, fixed-size and unique. Always also compare the real value (AND url = ...) to rule out a hash collision.

Remember

Use prefix indexes where an index would otherwise be impossible or very large.
Choose the length from real data, using the COUNT(DISTINCT LEFT(...)) query.
Do not expect covering, sorting or full uniqueness from them.

lightbulb

Prefix on both sides of the story

Prefix indexes serve WHERE url = '...' and WHERE url LIKE 'https://example.com/blog%'. They do not help LIKE '%mysql%'.