[Koha-devel] MySQL/MariaDB index prefix length

[David Cook]

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

 

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.koha-community.org/pipermail/koha-devel/attachments/20221209/41966a53/attachment.htm>