[wp-hackers] Optimize Post Meta Query

[Ryan Boren]

On Wed, Apr 29, 2009 at 4:02 PM, Joseph Scott <joseph at josephscott.org> wrote:
>
> On Apr 29, 2009, at 4:10 PM, Dan Fellars wrote:
>
>> I use the postmeta table pretty extensively for CMS-like capabilities  (
>> I have over 200k postmeta rows ).  In trying to find the culprit that is
>> causing a long load time on edit post pages, (using sqlmon plugin), I
>> found it all comes down to a single query - that I've seen take 10+
>> seconds to return  (even when I run it in a database client outside of
>> wp ):
>>
>
>
> I might be missing a detail here, but seems like the obvious thing to do is
> include the post_id field in the WHERE clause.  Since you said this is on
> the edit post screen already there's no point in getting postmeta for other
> posts.  The post_id field is indexed as well, which will help speed things
> up.  The EXPLAIN results of your original query indicate that the meta_key
> index isn't directly usable (isn't listed as a possible key), but is used
> any way because it's likely faster than a full table scan.  When I added
> post_id to your query it used that index instead and EXPLAIN dropped the
> number of estimated rows dramatically (down to 7 from 237) since it can
> focus on just the rows for that post.

meta_form() does that query to create a list of all meta keys
(regardless of post) except for ones starting with an underscore.
This is used in the Custom Fields box to populate a dropdown of
existing keys.  To keep that behavior we can't limit by post, but the
query could definitely be improved.