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] => =
                )

        )

)

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] => =
                )

        )

)

• wp-query
• database
• meta-query

• 3 wp_postmeta already has indexes on post_id, meta_key and meta_id. There's only one other column: meta_value. So you'll be indexing every column. This doesn't make sense. See this answer to a relevant question on SE for the reasons why you shoudn't do this. You're not just going to make WordPress magically faster by indexing meta_value. If that would work it would be in core. If you're doing such complex meta queries on that much data, you should be considering a custom table, not modifying core tables. – Jacob Peattie Commented Feb 1, 2018 at 12:22
• @JacobPeattie I think this makes perfect sense. Most of the time we don't have option to use custom table because we use plugins. Take woocommerce for example. Any types of comparison of the meta value will trigger full table scan as it doesn't have index. – Sisir Commented Aug 21, 2022 at 6:18

2 Answers 2

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.

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.