Fix slow query when checking Number as string-CodePudding

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?

CodePudding user response：

WordPress's metadata is (as you pointed out) stringly typed: everything's a text string. And, meta_value columns, as well as option_value columns, are potentially very long strings in TEXT 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.

   $args = [
      'post_type'  => 'post',
      'meta_query' => [
        [
          'key'     => '_customer_user',
          'value'   => 30,
          'type'    => 'NUMERIC',
          'compare' => '=',
        ],
      ],

    ];
    $query  = new \WP_Query( $args );

This makes WordPress generate a SQL query something like this.

SELECT SQL_CALC_FOUND_ROWS
       wp_posts.*
  FROM wp_posts
 INNER JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id )
 WHERE 1=1
   AND wp_postmeta.meta_key = '_customer_user'
   AND CAST(wp_postmeta.meta_value AS SIGNED) = '30' 
   AND wp_posts.post_type = 'post'
   AND (     wp_posts.post_status = 'publish' 
          OR wp_posts.post_status = 'private')
 GROUP BY wp_posts.ID
 ORDER BY wp_posts.post_date DESC
 LIMIT 0, 20;

Obviously suboptimal. (I'm being polite.)

A solution might be to create a virtual column in wp_postmeta, and then index it, like this.

ALTER TABLE wp_postmeta ADD COLUMN meta_value_int INT AS (
       IF(meta_value REGEXP '^[0-9] $',CAST(`meta_value` AS SIGNED),NULL)) 
       VIRTUAL;
ALTER TABLE wp_postmeta ADD INDEX meta_value_int (meta_value_int);

Then a query containing this rewritten WHERE clause could exploit the index.

   AND wp_postmeta.meta_value_int = 30

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 the meta_values that aren't numbers.

It is possible to create a function index, on MySQL >= 8.0.13 (not on MariaDB), this way.

ALTER TABLE wp_postmeta ADD INDEX meta_int_value 
     ((IF(`meta_value` REGEXP '^[0-9] $',CAST(`meta_value` AS SIGNED),NULL)));

But getting queries to use such an index on a bizarre function is hard.

CodePudding user response：

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 an INDEX.

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