MySQL Schema::getNormalizedIndexes() still assumes old, unsupported databases

Problem/Motivation

CR https://www.drupal.org/node/2510940 introduced a limitation on index length because of database limitations at the time.

These limitations are long gone from the minimum supported engine versions, as they have both moved on to use the DYNAMIC row format as the default. However, you can still run into the limitation if someone manually changed their tables to use the COMPACT row format.

Drupal still adds the 191 length limitation on all indexes, though, even when most sites no longer have said limitation. So if someone adds an index on a 'list_string' or other varchar field with default length of 255, it actually cuts the indexed field to a lower size, making it not function. It even makes matters worse, because an index that can never cover the requested data will be consulted for no reason, slowing the query down more than if there were no index at all.

Because this happens silently, people may think they are improving their database performance by adding an index, but are actually making it worse.

Steps to reproduce

Add an index on a list_string base field and see it cut to 191 characters, killing performance.

Proposed resolution

Either refactor Schema::getNormalizedIndexes() to run the following query from the docs and then only add the limit if the row format is COMPACT or REDUNDANT:

mysql> SELECT NAME, ROW_FORMAT FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME='test1/t1';
+----------+------------+
| NAME     | ROW_FORMAT |
+----------+------------+
| test1/t1 | Dynamic    |
+----------+------------+

Or, do not add the 191 character limit at all unless a setting is specified where you opt in to this behavior.

Remaining tasks

Decide what to do and do it

User interface changes

Introduced terminology

API changes

Data model changes

Index size may increase, for the better.

Release notes snippet