4

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

        )

)
  • 1
    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 Feb 1 '18 at 12:22
0

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.

  • It seems to work for adding an index to meta_value. But it made my queries take 13 seconds instead of 3. :/ Don´t really understand why.. But i´ll try your suggestion regarding the EXPLAIN and see if that can help me. Thanks for your help! – superhenke Feb 1 '18 at 12:12
  • Most likely it is now using a less relevant index - see about adding a multi-field index like the second one I mentioned and see what index performs best, try index hints + Explain. – janh Feb 1 '18 at 12:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.