Context

On lib/xmldb/xmldb_index.php there is a limitation in number of bytes to 999.

Rationale

I found the issue MDL-29314 where it seems to be added, so that Moodle ensures the things are done properly, no matter if we use the XMLDB Editor or programatically.

According to the Moodle Dev Chat conversation related to this, it seems to be added as the minimum value supported by all RDBMS: mysql, oracle, postgresql, etc.

Limitations

In normal cases it is sufficient this size in bytes, so that there is no problem at all.

However, there can be edge cases where this number of bytes is insufficient and has to be changed (modifying core in our instances) so that indexes are created correctly as expected.

As Eloy Lafuente told in the Chat, "there are techniques to avoid indexing that stuff, say ids/sequence mapping table with unique index on values, say hashing the values…".

All these are valid points of view.

Example of limitation

I needed to create an index from a local plugin that loads data from external systems, using 2 columns, column1 as VARCHAR(100) and column2 as VARCHAR(255). Both columns are a kind of idnumber values that we cannot control from Moodle. For performance, we needed to create this index on the Moodle database.

Trying to create an index like this, Moodle "says":

Default exception handler: Coding error detected, it must be fixed by a programmer: Invalid index definition in table {table_name}: the composed index on fields "column1,column2" is too long. Limit is 999 bytes / 333 chars. Debug: 

Error code: codingerror

* line 423 of /lib/ddl/sql_generator.php: coding_exception thrown

* line 358 of /lib/ddl/sql_generator.php: call to sql_generator->getCreateIndexSQL()

* line 226 of /lib/ddl/mysql_sql_generator.php: call to sql_generator->getCreateTableSQL()

* line 250 of /lib/ddl/sql_generator.php: call to mysql_sql_generator->getCreateTableSQL()

* line 417 of /lib/ddl/database_manager.php: call to sql_generator->getCreateStructureSQL()

* line 372 of /lib/ddl/database_manager.php: call to database_manager->install_from_xmldb_structure()

* line 774 of /lib/upgradelib.php: call to database_manager->install_from_xmldb_file()

* line 1985 of /lib/upgradelib.php: call to upgrade_plugins()

* line 494 of /lib/installlib.php: call to upgrade_noncore()

* line 486 of /lib/phpunit/classes/util.php: call to install_cli_database()

* line 149 of /admin/tool/phpunit/cli/util.php: call to phpunit_util::install_site()

Things that can be addressed

1. Include this value (999 nowadays) as an item to review on every Moodle version requirement, so that it meets the maximum value allowed by all RDBMS systems supported on that Moodle version. Therefore, it presents at every moment the maximum flexibility from a system administrator viewpoint.
2. Allow some private $CFG attribute configurable from config.php to force a different value, other than 999. A change on this configuration would not modify core in our Moodle instances. I thougth on the first place making this item an Administration setting from web, but since Andrew Lyons informed on the chat that this kind of item is not suitable for that, maybe it can be set up from $CFG like other stuff we currently have.
3. As Eloy alerted, usign utf8 mb4, the calculation of characters should be dividing by 4 the number of bytes (and not /3 like currently it is).
4. Also, allow XMLDB Editor to define prefixed indexes to be built. Like in mysql, so it would allow to say column1(100),column2(200) on the list of fields setting while creating an index. Prefixes defined for any field should be measured in bytes. So, some kind of SQL like CREATE INDEX idx_index_name ON table_name (column1(100),column2(200)) would be allowed. This will also help on this edge cases where the current limit of bytes in use for the index is exceeded.