3

In MySQL "An index key for a single-column index can be up to 767 bytes. The same length limit applies to any index key prefix."

Reviewing and searching the MariaDB documentation I cannot find a reference to this, or a similar, limit.

What is this limit?
What is the link to the documentation page I didn't find?

MichaelRpdx
  • 233
  • 1
  • 3
  • 11

1 Answers1

3

It is a bit hidden, but e.g.

https://mariadb.com/kb/en/mariadb/documentation/storage-engines/converting-tables-from-myisam-to-innodb/

says:

The maximum length of an INDEX is different between the Engines. [...] MyISAM allows 1000 bytes; InnoDB allows 767 bytes [...]

So the limit is engine specific, but the limits are not different between MySQL and MariaDB.

PS: if in doubt MySQL and MariaDB are still similar enough that anything that is not explicitly documented as being different is still the same

Hartmut Holzgraefe
  • 2,585
  • 12
  • 14
  • What could be the internal reason for enforcing this limit, what could be the drawback by following the workaround? – TechEnthusiast May 15 '18 at 10:35
  • 1
    The reason for limiting index length is to guarantee that a B-Tree Index page can contain a sufficiently large number of entries. In theory entries could be almost as large as an index page, but that leads to very bad performance as tree pages would need to be reorganized on every data change. The actual limits are a bit arbitrary, and where chosen based on performance observations. – Hartmut Holzgraefe May 16 '18 at 10:53
  • I was referring to [innodb_large_prefix](https://mariadb.com/kb/en/library/xtradbinnodb-server-system-variables/#innodb_large_prefix) – TechEnthusiast May 16 '18 at 15:22