Index length optimization in 4.2 made one of the core queries unable to use it

[viciousjack]

In 4.2 a few indexes got their length cut, which makes sense. But because of this particular case here:
​https://github.com/WordPress/WordPress/blob/974ca19e36dd71e7c506de05ca862d29a134e6dc/wp-admin/includes/upgrade.php#L2564

(meta_key index in postmeta table)

this core query can no longer use it:
​https://github.com/WordPress/WordPress/blob/974ca19e36dd71e7c506de05ca862d29a134e6dc/wp-admin/includes/template.php#L680

The actual query that's executed:

SELECT meta_key
FROM wp_postmeta
GROUP BY meta_key
HAVING meta_key NOT LIKE '\\_%'
ORDER BY meta_key
LIMIT 30

It needs to do full table traversal, and can do that inside index, but only if it's a full index.

EXPLAIN results.

Pre 4.2 :

SIMPLE	wp_postmeta	index	meta_key	meta_key	768	NULL	336384	Using index

query finishes in 249 ms.

Post 4.2:

SIMPLE	wp_postmeta	ALL	NULL	NULL	NULL	NULL 	6793853	Using temporary; Using filesort

query finishes in 46.1 seconds. (~7kk rows in the table)

MYSQL version 5.6.24

[dd32]

Can you let us know what charset your tables are using? utf8, utf8mb4, or something else? (Just to confirm, I'm almost certain it'll be utf8)

[viciousjack]

Replying to dd32:

Can you let us know what charset your tables are using? utf8, utf8mb4, or something else? (Just to confirm, I'm almost certain it'll be utf8)

Hey, it's UTF-8, yes.

[dd32]

Confirmed. Unfortunately I can't see a way to restore the index use without also decreasing the field length to 191 characters.

[dd32]

For the record, this applies to all charsets, including utf8mb4 & latin1.

[viciousjack]

Replying to dd32:

Confirmed. Unfortunately I can't see a way to restore the index use without also decreasing the field length to 191 characters.

Ok. And why going back to the uncut index length (as it was prior 4.2) isn't an option ?

[dd32]

And why going back to the uncut index length (as it was prior 4.2) isn't an option ?

The index length was cut to allow a single index be used on both utf8 & utf8mb4. MySQL requires (on InnoDB without the innodb_large_prefix configuration enabled) that the index be a maximum length of 767 bytes, which on a utf8mb4 column is 191 characters & 255 characters on a utf8 column.

Leaving the index set as unbounded has issues for the utf8 to utf8mb4 conversion. There's some background in #31869 and #21212

[dd32]

Duplicate of #32449 - See also #24498