Database: wp_postmeta - meta_key index

[wishe]

The index on the wp_postmeta table for the meta_key column is set to VARCHAR(191), While the column is VARCHAR(255). This has a huge performance impact on queries that use the wp_postmeta table.

In testing i noticed a performance increase on large websites with millions of records of several 100% when removing the index size on the column.

This seems like a very obvious improvement and the issue has surely been raised before? So if there is a reason this cannot be altered without breaking something else I would like to know what that is.

If you require any more information, let me know.

[rafalsokolowski]

Confirmed. Removing the index prefix length speeds up queries on wp_postmeta table.

Is it safe to remove it if the longest meta_key record in the table is shorter than 191?
Also, is it related to database encoding (utf8mb4)?

[iCaleb]

Related comment in core: ​https://github.com/WordPress/WordPress/blob/1b7cd950ca6807996996e9a30c98298eb5073bce/wp-admin/includes/schema.php#L49-L51

[wishe]

Replying to iCaleb:

Related comment in core: ​https://github.com/WordPress/WordPress/blob/1b7cd950ca6807996996e9a30c98298eb5073bce/wp-admin/includes/schema.php#L49-L51

This explains the reasoning of why it was done, but with the extreme performance hit that this causes there has to be another solution.

I ran a few tests today and the results are absolutely shocking.

This is the query with the index restricted to 191

This is after removing the character limit on the index

This is running on a large WooCommerce store with close to 2 million records in the wp_postmeta table. And the difference is staggering

[wishe]

So after some additional research. I found some information in the mysql documentation.

If innodb_large_prefix is enabled (the default), the index key prefix limit is 3072 bytes for InnoDB tables that use the DYNAMIC or COMPRESSED row format. If innodb_large_prefix is disabled, the index key prefix limit is 767 bytes for tables of any row format.

innodb_large_prefix is deprecated; expect it to be removed in a future MySQL release. innodb_large_prefix was introduced in MySQL 5.5 to disable large index key prefixes for compatibility with earlier versions of InnoDB that do not support large index key prefixes.

The index key prefix length limit is 767 bytes for InnoDB tables that use the REDUNDANT or COMPACT row format. For example, you might hit this limit with a column prefix index of more than 255 characters on a TEXT or VARCHAR column, assuming a utf8mb3 character set and the maximum of 3 bytes for each character.

As you can see for MySQL 5.6-7 with the default setting innodb_large_prefix we can support index sizes of 768(3072/4) characters. As long as the row format is set to DYNAMIC or COMPRESSED

Then in MySQL 8.0 (current):

The index key prefix length limit is 3072 bytes for InnoDB tables that use DYNAMIC or COMPRESSED row format.

The index key prefix length limit is 767 bytes for InnoDB tables that use the REDUNDANT or COMPACT row format.

For example, you might hit this limit with a column prefix index of more than 191 characters on a TEXT or VARCHAR column, assuming a utf8mb4 character set and the maximum of 4 bytes for each character.

In the latest version the innodb_large_prefix is no longer needed and it all depends on the row format that is set. In my case, without changing anything, my default database had all rows set to DYNAMIC so changing the prefix length was seamless.

As can also be read from the documentation

The default row format for InnoDB tables is defined by innodb_default_row_format variable, which has a default value of DYNAMIC.

So according to the MySQL manual, with default settings MySQL actually supports index sizes up to 768(3072/4) characters assuming utf8mb4.

So this for me raises the question further, why are we limiting the index sizes to 191 characters? Is this to support more database setups? Older database setups? Or is there something that I am missing in my interpretation of the problem?