WordPress.org

Make WordPress Core

Opened 6 years ago

Closed 6 years ago

#32649 closed defect (bug) (duplicate)

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

Reported by: viciousjack Owned by:
Milestone: Priority: normal
Severity: normal Version: 4.2
Component: Database Keywords:
Focuses: Cc:

Description

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

Change History (7)

#1 follow-up: @dd32
6 years ago

  • Version changed from 4.2.2 to 4.2

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)

#2 in reply to: ↑ 1 @viciousjack
6 years ago

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.

#3 follow-up: @dd32
6 years ago

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

#4 @dd32
6 years ago

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

#5 in reply to: ↑ 3 @viciousjack
6 years ago

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 ?

#6 @dd32
6 years ago

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

#7 @dd32
6 years ago

  • Milestone Awaiting Review deleted
  • Resolution set to duplicate
  • Status changed from new to closed

Duplicate of #32449 - See also #24498

Note: See TracTickets for help on using tickets.