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: |
|
Owned by: |
|
---|---|---|---|
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)
#2
in reply to:
↑ 1
@
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 yourmeta_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
@
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.
See also #27272.
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 themeta_value
from yourmeta_query
is 12 characters? (These are genuine questions - I don't know, and I'm having a hard time finding documentation about it.)