Indexing Text Columns in MySQL 8.4

Text columns are often indexed by habit: either the entire VARCHAR is added to a B-tree, or an arbitrary prefix such as title(20) is chosen to make the index smaller. Neither choice is universally optimal. The useful index is the smallest one that still supports the queries, ordering, constraints, and response times the application actually needs.

MySQL 8.4 gives us enough information to make that decision from data rather than folklore. The practical workflow is to begin with the access pattern, measure how much discrimination each prefix retains, build a candidate index, and verify the result with EXPLAIN ANALYZE on production-shaped data.

Start with the Query, Not the Column Type

Suppose a publishing system stores posts like this:

CREATE TABLE post (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    status ENUM('draft', 'published') NOT NULL,
    title VARCHAR(300) NOT NULL,
    body MEDIUMTEXT NOT NULL,
    PRIMARY KEY (id)
) ENGINE=InnoDB
  DEFAULT CHARACTER SET utf8mb4
  COLLATE utf8mb4_0900_ai_ci;

The declaration VARCHAR(300) is a capacity limit, not an instruction to index 300 characters. Before adding an index, list the statements it must support:

SELECT id, title
FROM post
WHERE title = ?;

SELECT id, title
FROM post
WHERE status = 'published'
  AND title = ?;

The first query suggests an index beginning with title. The second may benefit more from (status, title). If the application instead searches for words anywhere in body, a normal B-tree prefix is the wrong structure; that workload belongs to a FULLTEXT index or a dedicated search system. Likewise, LIKE '%database%' cannot seek into an ordinary B-tree because the leading value is unknown.

The Full-Column Index Is the Baseline

For an ordinary VARCHAR that fits within the storage engine's key limits, begin with the simplest correct design:

CREATE INDEX post_title ON post (title);

A full index supports equality and range lookups, preserves the complete collation-defined ordering, and can contain the complete title for an index-only plan when the other selected columns permit one. It is often the right answer. A prefix should replace it only when the reduction in index size and write cost is material and the loss of information does not hurt the workload.

Every InnoDB secondary-index entry also carries the primary-key value needed to locate the clustered row. Shortening one text key therefore does not shrink the whole entry in direct proportion to the character count. Measure the resulting index rather than estimating savings from the declared VARCHAR width.

What a Prefix Index Actually Does

A prefix index stores only the leading part of each value:

CREATE INDEX post_title_prefix ON post (title(24));

For nonbinary strings, 24 means characters in the SQL definition. The engine's key limit is measured in bytes. With utf8mb4, one character can require several bytes, so “24 characters” must not be described as a 24-byte index. Binary string prefixes use bytes directly.

For InnoDB tables using the modern DYNAMIC or COMPRESSED row format, the documented maximum index key length is 3072 bytes. Older COMPACT and REDUNDANT formats have a 767-byte limit. The limit applies to the complete key, so every part of a composite index consumes the same byte budget. Confirm the actual table definition and server configuration instead of treating 3072 bytes as a portable schema constant.

An equality predicate still compares the complete column value:

SELECT id, title
FROM post
WHERE title = 'A Practical Guide to InnoDB Indexes';

MySQL can use the stored prefix to reject most rows, then examine the complete values of the remaining candidates. If many titles share that prefix, those extra candidate checks are the price of the smaller index. The prefix also cannot cover a query that needs the complete title, and it cannot by itself establish the full ordering among values that share the indexed prefix.

Measure Prefix Discrimination

The old shortcut was to increase the prefix until every current row became unique. That overfits one snapshot and behaves badly when duplicate full values are legitimate. Two measurements are more useful:

Distinctness retained compares distinct prefixes with distinct complete values. It reveals how much information the prefix loses.
Row selectivity compares distinct prefixes with row count. It approximates how narrowly a typical equality lookup can divide the table, although skew still matters.

MySQL 8 supports a recursive common table expression, so no information_schema row-generation trick or custom function is needed:

WITH RECURSIVE prefix_length (chars) AS (
    SELECT 1
    UNION ALL
    SELECT chars + 1
    FROM prefix_length
    WHERE chars < 64
),
column_stats AS (
    SELECT
        COUNT(*) AS row_count,
        COUNT(DISTINCT title) AS full_distinct
    FROM post
    WHERE title IS NOT NULL
)
SELECT
    prefix_length.chars AS prefix_chars,
    COUNT(DISTINCT LEFT(post.title, prefix_length.chars)) AS prefix_distinct,
    column_stats.full_distinct,
    ROUND(
        100.0 * COUNT(DISTINCT LEFT(post.title, prefix_length.chars))
        / NULLIF(column_stats.full_distinct, 0),
        2
    ) AS distinctness_retained_pct,
    ROUND(
        100.0 * COUNT(DISTINCT LEFT(post.title, prefix_length.chars))
        / NULLIF(column_stats.row_count, 0),
        2
    ) AS row_selectivity_pct
FROM prefix_length
CROSS JOIN post
CROSS JOIN column_stats
WHERE post.title IS NOT NULL
GROUP BY
    prefix_length.chars,
    column_stats.full_distinct,
    column_stats.row_count
ORDER BY prefix_length.chars;

The query is an audit, not something to run on every request. Each tested length performs a distinct-value aggregation, so execute it on a representative snapshot, a replica, or bounded primary-key ranges when the production table is large.

Look for the elbow where additional characters produce little extra discrimination. A result such as 92%, 98.7%, 99.6%, 99.7% suggests that the last increment may not justify a wider key. There is no universal target percentage: one extra candidate per lookup may be irrelevant, while a hot join executed millions of times may justify the full key.

Collation Defines What “Distinct” Means

COUNT(DISTINCT LEFT(title, n)) uses the column's collation, just like the index. Under utf8mb4_0900_ai_ci, comparisons are case-insensitive and accent-insensitive. Values that look different byte for byte may therefore belong to the same index comparison class. This is desirable when it matches the product's equality semantics and misleading when it does not.

Do not force a binary collation into the audit merely to produce a more impressive percentage. Measure with the same character set, collation, and expressions used by the real predicate. If identifiers must be case-sensitive while display titles are not, model those as different semantics, possibly different columns, rather than expecting one index to implement both.

Composite Indexes Usually Matter More

A single-column selectivity table cannot decide the order of a composite index. Column order follows the predicates and sort order of the query:

CREATE INDEX post_status_title
    ON post (status, title(24));

This index is appropriate for equality on status followed by title lookup. It does not replace post_title for queries that search by title alone, because the leftmost key part is missing. Conversely, adding separate indexes on status and title is not automatically equivalent to the composite index. MySQL can combine some indexes, but one purpose-built access path is often cheaper and can preserve useful ordering.

Low cardinality is not a reason to ban a column from the first position. A two-value status can still be exactly the right leading key part when every relevant query fixes one status before applying a selective title condition.

Unique Prefixes Are a Different Constraint

A unique prefix index does not mean “full values must be unique”:

CREATE UNIQUE INDEX post_title_unique_prefix
    ON post (title(24));

It means the first 24 collation-aware characters must be unique. Two different long titles with the same prefix are rejected even when their remaining characters differ. Use a full UNIQUE index when uniqueness belongs to the complete value. If the complete value cannot be indexed directly, enforce the requirement with a deliberately designed surrogate and still verify the original value.

Digest Indexes for Very Long Exact Values

When values are very long and the dominant operation is byte-identical equality, a fixed-width digest can be useful. A generated column keeps the digest synchronized automatically:

ALTER TABLE document
    ADD COLUMN content_sha256 BINARY(32)
        GENERATED ALWAYS AS (UNHEX(SHA2(content, 256))) STORED,
    ADD INDEX document_content_sha256 (content_sha256);

The lookup must retain a comparison against the original value:

SELECT id
FROM document
WHERE content_sha256 = UNHEX(SHA2(?, 256))
  AND BINARY content = BINARY ?;

The second predicate is the collision guard; a digest is a candidate locator, not proof that two arbitrary values are equal. A digest index supports equality only. It cannot provide lexical range scans, prefix search, or ordering by the original text.

Hashing also changes comparison semantics. SHA2('Résumé', 256) and SHA2('resume', 256) differ even though an accent-insensitive, case-insensitive collation may compare the strings as equal. Use this pattern only for byte-identical equality or hash a separately defined canonical representation whose normalization rules are part of the application contract. A CRC32 value is too small for a large candidate set, and MD5's 16-byte output is no longer a sensible default when SHA-256 is readily available.

Functional Indexes for Expression-Based Lookups

MySQL 8 can index an expression directly:

CREATE INDEX account_email_lower
    ON account ((LOWER(email)));

This can support a predicate using the matching expression:

SELECT account_id
FROM account
WHERE LOWER(email) = LOWER(?);

The expression and result type must match the indexed definition closely enough for the optimizer to recognize it. A functional index is redundant when the existing collation already gives the required case-insensitive comparison, and it still has the storage and write cost of an ordinary secondary index. Use it to represent a genuine query expression, not as decoration around a column that was already searchable.

Inspect the Index MySQL Created

INFORMATION_SCHEMA.STATISTICS reports the key parts, configured prefix length, estimated cardinality, and visibility:

SELECT
    index_name,
    seq_in_index,
    column_name,
    sub_part,
    cardinality,
    is_visible
FROM information_schema.statistics
WHERE table_schema = DATABASE()
  AND table_name = 'post'
ORDER BY index_name, seq_in_index;

SUB_PART shows the configured prefix for a prefixed key part. CARDINALITY is an estimate, not an exact count. Refresh persistent InnoDB statistics after creating an index:

ANALYZE TABLE post;

For a page-count estimate per InnoDB index, installations with access to the internal statistics table can inspect:

SELECT
    index_name,
    stat_value AS index_pages
FROM mysql.innodb_index_stats
WHERE database_name = DATABASE()
  AND table_name = 'post'
  AND stat_name = 'size'
ORDER BY index_name;

Multiply pages by @@innodb_page_size for an approximate byte count. Compare after statistics have settled and after loading production-shaped data; a nearly empty development table says little about page density, cache behavior, or write amplification.

Test a Candidate Without Committing to It

Invisible indexes are maintained by InnoDB but ignored by the optimizer by default. They are useful for introducing a candidate without changing ordinary query plans:

CREATE INDEX post_title_prefix_candidate
    ON post (title(24)) INVISIBLE;

A session or one statement can opt into invisible indexes while evaluating the candidate:

EXPLAIN ANALYZE
SELECT /*+ SET_VAR(optimizer_switch='use_invisible_indexes=on') */
       id, title
FROM post
WHERE title = 'A Practical Guide to InnoDB Indexes';

EXPLAIN ANALYZE executes the statement and reports estimated and actual rows, loops, and iterator timing. Use it carefully with statements that can modify data; the example is a read-only SELECT. Test common values, rare values, long shared prefixes, absent values, and realistic joins. Inspect candidate rows after the index lookup, not just whether the index name appears in the plan.

When the candidate wins consistently, make it visible:

ALTER TABLE post
    ALTER INDEX post_title_prefix_candidate VISIBLE;

Before dropping an existing index, make that old index invisible and observe the real workload, slow query log, and Performance Schema. Visibility changes are cheap and reversible; rebuilding a dropped index on a large table is not.

A Practical Decision Process

Write down the exact equality, range, ordering, join, and uniqueness requirements.
Use the full-column or correct composite index as the correctness baseline.
Measure prefix discrimination under the real collation on representative data.
Choose a candidate near the discrimination plateau, not an arbitrary round number.
Create the candidate as invisible, refresh statistics, and run production-shaped plans.
Compare actual rows, latency, index pages, cache pressure, and write throughput.
Retain the simplest index that satisfies the workload with useful margin.

The optimal text index is therefore not one magic prefix length. It is a measured compromise among discrimination, key width, collation semantics, query coverage, ordering, and maintenance cost. On many modern MySQL schemas the full VARCHAR index remains best. Prefix and digest indexes are valuable tools when their narrower semantics match a proven bottleneck.

References

MySQL-INDEXOracle. MySQL 8.4 Reference Manual. “CREATE INDEX Statement” and “Column Indexes.”
MySQL-EXPLAINOracle. MySQL 8.4 Reference Manual. “EXPLAIN Statement.”
MySQL-INVISIBLEOracle. MySQL 8.4 Reference Manual. “Invisible Indexes.”
MySQL-GENERATEDOracle. MySQL 8.4 Reference Manual. “CREATE TABLE and Generated Columns.”