Understanding Prefix Indexes in MySQL: Concepts, Use Cases, and Performance Testing

A prefix index (also called a partial index) stores only the leading characters of a column, such as indexing the first 10 characters of an ID number, which can significantly reduce the size of the index file.

Using a prefix index can shrink index files, allowing more index entries per page and speeding up look‑ups, but it cannot be used for ORDER BY or GROUP BY operations and cannot serve as a covering index.

Prefix indexes are appropriate when the column values are long and the first few characters are already highly selective; they are unsuitable when a short prefix still yields many duplicate values, making the index ineffective.

The guiding principle is to lower the duplication of index values. For example, if many rows have an area field that starts with "china", indexing only the first 1‑5 characters would produce many identical index entries, reducing query efficiency.

To illustrate the impact, a test table x_test was created and populated with two million rows:

CREATE TABLE `x_test` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `x_name` varchar(255) NOT NULL,
  `x_time` int(10) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO x_test(x_name,x_time)
SELECT CONCAT(rand()*3300102,x_name),x_time FROM x_test WHERE id < 30000;

Test 1 – No index : Querying a specific x_name value took 2.253 seconds.

SELECT * FROM x_test WHERE x_name = '1892008.205824857823401.800099203178258.8904820949682635656.62526521254';

Test 2 – Prefix index on the first character ( ALTER TABLE x_test ADD INDEX (x_name(1)); ): The same query now took 3.291 seconds because the one‑character prefix produced a large number of duplicate index entries (about 200 k rows share the same value).

ALTER TABLE x_test ADD INDEX (x_name(1));
SELECT * FROM x_test WHERE x_name = '1892008.205824857823401.800099203178258.8904820949682635656.62526521254';

Test 3 – Prefix index on the first four characters ( ALTER TABLE x_test ADD INDEX (x_name(4)); ): Query time dropped to 0.703 seconds, showing a substantial improvement as duplicate index values were reduced.

ALTER TABLE x_test ADD INDEX (x_name(4));
SELECT * FROM x_test WHERE x_name = '1892008.205824857823401.800099203178258.8904820949682635656.62526521254';

Test 4 – Prefix index on the first seven characters ( ALTER TABLE x_test ADD INDEX (x_name(7)); ): The query executed in only 0.014 seconds, because the seven‑character prefix provided enough uniqueness to avoid most duplicate index entries.

ALTER TABLE x_test ADD INDEX (x_name(7));
SELECT * FROM x_test WHERE x_name = '1892008.205824857823401.800099203178258.8904820949682635656.62526521254';

These results demonstrate that choosing an appropriate prefix length is crucial: a too‑short prefix may degrade performance, while a sufficiently selective prefix can dramatically speed up queries while keeping the index size small.