[wp-trac] [WordPress Trac] #53958: Database: wp_postmeta - meta_key index

[WordPress Trac]

#53958: Database: wp_postmeta - meta_key index
--------------------------+------------------------------
 Reporter:  wishe         |       Owner:  (none)
     Type:  defect (bug)  |      Status:  new
 Priority:  normal        |   Milestone:  Awaiting Review
Component:  Database      |     Version:  5.8
 Severity:  major         |  Resolution:
 Keywords:                |     Focuses:
--------------------------+------------------------------

Comment (by wishe):

 **So after some additional reasearch. 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.

 **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?**

-- 
Ticket URL: <https://core.trac.wordpress.org/ticket/53958#comment:4>
WordPress Trac <https://core.trac.wordpress.org/>
WordPress publishing platform