Add indexing to meta_value in wp_postmeta

Question

Is it poossible to add index to the meta_value column in the wp_postmeta table in order to make faster queries in Wordpress?

I have about 5 million rows in the wp_postmeta table and my queries takes about 3 seconds when limited to 500 rows.

I am trying to do add an index to meta_value in phpMyAdmin but i am getting an error message saying:

column 'meta_value' used in key specification without a key length

I was thinking of converting it to varchar(255) but i have a meta value of _wp_attachment_metadata that is around 1500 characters long.

My query look like this:

Array
(
    [posts_per_page] => 500
    [orderby] => name
    [order] => ASC
    [post_type] => company
    [post_status] => publish
    [meta_query] => Array
        (
            [relation] => OR
            [0] => Array
                (
                    [key] => example1
                    [value] => 2
                    [type] => numeric
                    [compare] => =
                )

            [1] => Array
                (
                    [key] => example2
                    [value] => 2
                    [type] => numeric
                    [compare] => =
                )

            [2] => Array
                (
                    [key] => example3
                    [value] => 2
                    [type] => numeric
                    [compare] => =
                )

            [3] => Array
                (
                    [key] => example3
                    [value] => 2
                    [type] => numeric
                    [compare] => =
                )

        )

)

Answer 1

Ollie Jones is doing massive index upgrade work with https://wordpress.org/plugins/index-wp-mysql-for-speed/ right now.

His current recommendation for modern Barracuda storage engine backed tables is

ALTER TABLE wp_postmeta
  ADD UNIQUE KEY meta_id (meta_id),
  DROP PRIMARY KEY,
  ADD PRIMARY KEY (post_id, meta_key, meta_id),
  DROP KEY meta_key,
  ADD KEY meta_key (meta_key, meta_value(32), post_id, meta_id),
  ADD KEY meta_value (meta_value(32), meta_id),
  DROP KEY post_id;

See https://www.plumislandmedia.net/index-wp-mysql-for-speed/tables_and_keys/ for details.

Answer 2

Don't limit the field, instead, limit the index, e.g.

ALTER TABLE wp_postmeta ADD key(meta_value(100))

This limits the index to the first hundred bytes of meta_value.

You'll probably want an index on post_id, meta_key, meta_value for joins. How much of meta_key and meta_value is required depends on your data, for example

ALTER TABLE wp_postmeta ADD key(post_id, meta_key(100), meta_value(100));

Whether that helps with your query is another question. Get the SQL generated with $query->request and then run it with "EXPLAIN $SQL" to see how MySQL handles it, which indexes are used etc pp.