Make WordPress Core

Opened 9 years ago

Closed 9 years ago

#36625 closed enhancement (fixed)

Don't CAST the post meta value to CHAR in meta query

Reported by: ericlewis's profile ericlewis Owned by: boonebgorges's profile boonebgorges
Milestone: 4.6 Priority: normal
Severity: normal Version: 3.1
Component: Query Keywords:
Focuses: Cc:

Description

This topic came up in 19738, and warrants its own ticket.

Before r15724 (see #9124), if you had a post with meta for rating of 100 and performed a meta query looking for posts with a rating higher than 50, the query would not return that post. This is because the MySQL generated for WP_Query performed a string comparison, as both sides of the comparison were strings, e.g. wp_postmeta.meta_value > '50'.

This led to the introduction of the meta query type parameter. Provide a MySQL data type, and the meta value is cast to that type in the SQL query.

A default was set to cast the meta_value column to CHAR. Casting the meta value to CHAR is redundant, as the meta_value column is LONGTEXT, which is already a string type.

Removing this cast in the SQL generated by WP_Query allows users to add an index for the meta_value column and have that index considered for use by the MySQL optimizer on execution.

Change History (4)

#1 follow-up: @boonebgorges
9 years ago

See also #27272.

Removing this cast in the SQL generated by WP_Query allows users to add an index for the meta_value column and have that index considered for use by the MySQL optimizer on execution.

Can we get some more technical details on how the optimizer decides whether the index can be used? When you add an index to the meta_value LONGTEXT column, you'll have to specify an index length. How does MySQL decide whether that index can be used? What happens when your index is 8 characters, and the meta_value from your meta_query is 12 characters? (These are genuine questions - I don't know, and I'm having a hard time finding documentation about it.)

#2 in reply to: ↑ 1 @ericlewis
9 years ago

Replying to boonebgorges:

Can we get some more technical details on how the optimizer decides whether the index can be used?

The index is the column value's leftmost prefix, truncated to the index's length. The optimizer consider using the index except in some cases:

  • a function is applied to the index column's value in a where clause (i.e. CHAR(postmeta.meta_value).
  • a LIKE clause is used on the index column with a wildcard on the left-end of the search string (i.e. postmeta.meta_value LIKE "%erosmith").

There may be other cases an index would not be considered for use by the optimizer, or a better general comment on when indexes are considered for use (@barry, @pento ?)

What happens when your index is 8 characters, and the meta_value from your meta_query is 12 characters?

If the query is meta_value = "Aerosmith", and meta_value has an index with an 8 character length, MySQL would use the index to figure out possible rows to touch in the actual table (the "Aerosmith" row being one of them), and rule out rows to touch in the actual table (i.e. a row with a meta_value of Van Halen) based on partial string matches.

(These are genuine questions - I don't know, and I'm having a hard time finding documentation about it.)

These are great questions I'm not finding answer to in the MySQL reference either. I opened mysql#81181 to discuss updating the reference with more detail here.

#3 @boonebgorges
9 years ago

  • Milestone changed from Awaiting Review to 4.6
  • Owner set to boonebgorges
  • Status changed from new to assigned

Thanks, @ericlewis. The details about index use are helpful. The question of whether WP should add an index to meta_value by default is complicated, but for the purposes of this ticket, the least we can do is stand out of the way of those who choose to add such indexes themselves. Let's make the change.

#4 @boonebgorges
9 years ago

  • Resolution set to fixed
  • Status changed from assigned to closed

In 37594:

In WP_Meta_Query, don't cast meta_value to CHAR.

CHAR is redundant, since the meta_value column is LONGTEXT. Meanwhile,
use of CAST() causes MySQL to ignore any index that the administrator may
have added to the column.

A number of automated tests were doing searches for CAST in the SQL strings
generated by WP_Meta_Query (for reasons unrelated to the CAST() behavior).
These tests have been updated to expect the new query format.

Props ericlewis.
Fixes #36625.

Note: See TracTickets for help on using tickets.