Hi all,

We often create indexes with a prefix length of 191 because we use 4 byte encoded fields. 191 * 4 = 764, which comes in under the prefix index length threshold of 767.

But that looks like a limit that was imposed due to the COMPACT row format. Most newer MySQL/MariaDB databases will be using the DYNAMIC row format, which can support an index key prefix limit of 3072 bytes (or 768 4-byte encoded characters).

On Bugzilla, there have been times where I’ve speculated that maybe it’s the sysadmin’s job to update the database row format, but perhaps we should take a more proactive effort to move row format from COMPACT to DYNAMIC, so that we can more systematically take advantage of longer index prefixes.

I often think about how to index URLs, since the maximum length of URLs are very large. Web browsers hover around the 2000 character mark while web servers can be much higher than that. Of course, even with 3072 bytes, that still wouldn’t be enough for a full URL. But it would get a person a lot farther than 191 characters…  

David Cook

Senior Software Engineer

Prosentient Systems

Suite 7.03

6a Glen St

Milsons Point NSW 2061

Australia

Office: 02 9212 0899

Online: 02 8005 0595