1 views collected around this technical thread.
This article explains why indexing long character columns can bloat MySQL indexes, introduces the concept of index selectivity, shows how to calculate the best prefix length with real‑world queries, and demonstrates performance differences between short and optimal index prefixes.
The article explains MySQL 5.7's engine‑dependent prefix index length limits, demonstrates how utf8mb4 reduces the maximum indexable column size for MyISAM tables, provides test cases with SQL code, and offers practical solutions such as prefix indexes, composite indexes, and CTAS for large datasets.
The article explains how to improve indexing of long string columns in MySQL by using generated columns with CRC32/MD5 hashes, functional indexes introduced in MySQL 8.0.13, and prefix indexes, providing code examples and performance considerations.
This article explains what a prefix (partial) index is in MySQL, why it can reduce index size and improve query speed, outlines suitable scenarios and limitations, and demonstrates performance differences through a series of tests with varying prefix lengths on a large test table.
This article explains the concept, creation, and performance benefits of MySQL prefix indexes, demonstrates how to choose an optimal prefix length using selectivity calculations, and presents practical techniques for handling both prefix and suffix search patterns.