0

I received an email from my server host telling me that I had to optimize my database requests which seem to be too slow.

Indeed, I use a lot of custom requests within Wordpress and these requests are pointing towards the "wp_postmeta" table. This is not ideal because you have to deal with the table structure as it is given.

Here are the columns within the 'wp-postmeta' table:

meta_id    (table id)
post_id    (related post which is a foreign key)
meta_key   (name of the parameter)
meta_value (value of that parameter)

meta_id is the default primary key of the table and there is NO index by default that is set by Wordpress.

I believe then that the best way to optimize the requests would be to assign an index to one of these columns.

So here is the most frequent request that I have:

SELECT meta_value FROM wp_postmeta WHERE meta_key = 'xxxx' and post_id = 1234

Knowing that:

  • A same post_id can have up to 15 different related meta_keys and there are about 900 different post_id's
  • A same meta_key can appear more than 150 times on the table records, whatever the post_id's

Which one of these two columns will be the best choice as the index of the table: post_id or meta_key?

Thank you.

Baylock
  • 1,246
  • 4
  • 25
  • 49

1 Answers1

2

I'd recommend creating an index based on both columns, given that your most frequent statement is 

SELECT FROM wp_postmeta WHERE meta_key = 'xxxx' and post_id = 1234

MySQL have some good documentation on the subject: http://dev.mysql.com/doc/refman/5.0/en/multiple-column-indexes.html

CREATE INDEX index_postmeta_postmeta ON wp_postmeta (post_id, meta_key)

But basically, what an index does, and why you should use it is explained here: What is an index in SQL?

Your local friendly DBA should be doing all this for you BTW :)

Also, this is my personal rule of thumb. I create indexes on ALL foreign keys on a table, because it's usually a natural look up column. So I'd probably also chuck in:

 CREATE INDEX index_postmeta_postid ON wp_postmeta (post_id)
Community
  • 1
  • 1
rurouni88
  • 1,165
  • 5
  • 10
  • Thank you very much for your answer but doesn't it ruin the purpose of an index? I mean two indexes when you have 10 columns, I can understand that but two indexes for only 4 columns (50% is indexed), will it really optimize the queries? Thank you. PS: there is no administrator, there is only me so I am the one that should take care of this. – Baylock Dec 18 '14 at 02:21
  • 1
    Nopes, because as you stated, your most frequent statement uses both those columns. So you don't want the platform performing a full table scan [when the platform looks at record 1 - current record] (which is what makes the query non-optimal) every time it executes that query. If needed, you can create separate indexes on both post_id and meta_key, if you're searching solely on those columns alone, frequently. Basically, you index based on your most frequently executed search conditions. – rurouni88 Dec 18 '14 at 02:27
  • Ok, I see your edited post. Now you tell me that 3 indexes on 4 columns is the way to go! I trust your experience and I'll do that but it really seems weird. To me it's like saying that you want to optimize the execution of 4 things by giving priority n°1 to each of these things. From my noob point of view, priority 1 to everything is like doing nothing eventually. But I'll do that because you know better. – Baylock Dec 18 '14 at 02:33
  • 1
    Eh? I've only suggested the creation of 2 indexes. index_postmeta_postmeta, which uses 2 columns. And index_postmeta_postid, which is an index on post_id. The last index is optional, as I said. But generally foreign keys are natural lookup points. As in you will query based on them. This is just a general rule of thumb :) – rurouni88 Dec 18 '14 at 02:37