WordPress stores both numbers and strings into Users and Posts meta tables. The issue comes when we search for meta_value:
When using a number the query takes 20 times longer then same query and string.
SLOW 0.73 seconds:
SELECT * from wp_postmeta AS pm WHERE pm.meta_key = '_customer_user' AND pm.meta_value = 30
FAST 0.0013 seconds:
SELECT * from wp_postmeta AS pm WHERE pm.meta_key = '_customer_user' AND pm.meta_value = '30'
There is a key for each column and combined key. I read it could be a problem with PACK_KEYS, if so than I still do not know how to change it.
select version();
8.0.31
show create table wp_postmeta;
CREATE TABLE `wp_postmeta` (
`meta_id` bigint unsigned NOT NULL AUTO_INCREMENT,
`post_id` bigint unsigned NOT NULL DEFAULT '0',
`meta_key` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`meta_value` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
PRIMARY KEY (`meta_id`),
KEY `post_id` (`post_id`),
KEY `meta_key` (`meta_key`(191)),
KEY `postid_key_value` (`post_id`,`meta_key`,`meta_value`(20)),
KEY `key_value` (`meta_key`,`meta_value`(20))
) ENGINE=InnoDB AUTO_INCREMENT=4285535 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
Explain SLOW:
SIMPLE pm NULL ref meta_key,key_value meta_key 767 const 46930 10.00 Using where
Explain FAST:
SIMPLE pm NULL ref meta_key,key_value key_value 1106 const,const 1 100.00 Using where
If I cannot fix those indexes in database, than maybe to use some WP filter to fix this kind of queries for Posts’ and Users’ meta?
EDIT:
Since there is no obvious solution, at least for WordPress we can try and correct similar queries using the query filter this way:
function sns_filter_queries( $query ) {
global $wpdb;
if( stripos($query, $wpdb->postmeta)!==FALSE || stripos($query, $wpdb->usermeta)!==FALSE ) {
$count = null;
$query = preg_replace('/meta_value[ ]+=[ ]+(\d+)/', 'meta_value = '${1}'', $query, 1, $count);
};
return $query;
}
add_filter( 'query', 'sns_filter_queries', 10, 1 );
It works fine on my side, hope it may help others.
2
Answers
pm.meta_value
is a string;30
is a number. Comparing them requires converting the string to a number, which means checking every row without being able to use anINDEX
.The other 3 combinations of testing string versus number can work efficiently (that is may be able to use an
INDEX
.)The solution is to always provide
meta_value
values as strings.See also this plugin for efficiency in
postmeta
: WP Index Improvements However, it looks like you have already implemented similar indexes. But… I suggest that you DROP KEYpost_id
(post_id
) and KEYmeta_key
(meta_key
(191)) since they are redundant and/or in the way. (The 191 is no longer relevant since you are using Mysql 8.0.)WordPress’s metadata is (as you pointed out) stringly typed: everything’s a text string. And,
meta_value
columns, as well asoption_value
columns, are potentially very long strings inTEXT
column types. The tables were designed this way almost 20 years ago for ultimate flexibility, but this design scales up poorly (as you pointed out).If you used WordPress’s built-in WP_Meta_Query subsystem to generate the query in your question, you would use something like this.
This makes WordPress generate a SQL query something like this.
Obviously suboptimal. (I’m being polite.)
A solution might be to create a virtual column in wp_postmeta, and then index it, like this.
Then a query containing this rewritten WHERE clause could exploit the index.
This solution requires rewriting numeric queries to use the virtual column. That’s possible using WordPress’s get_meta_sql filter. It’s conceivable a plugin could do this, adding virtual columns and indexes for all the various datatypes WordPress stashes in those meta_value columns: NUMERIC, BINARY, DATE, DATETIME, DECIMAL, SIGNED, TIME, UNSIGNED. It sounds like a complex plugin.
It would be nice if it were possible to create and use a function index to speed up access to those stringly typed integers, maybe on
(CAST(meta_value AS SIGNED))
. But that simple expression won’t work because of the need to ignore all themeta_value
s that aren’t numbers.It is possible to create a function index, on MySQL >= 8.0.13 (not on MariaDB), this way.
But getting queries to use such an index on a bizarre function is hard.