[wp-hackers] Optimize Post Meta Query

[Joseph Scott]

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.


>
>
> SELECT meta_key
> FROM wp_postmeta
> WHERE meta_key NOT LIKE '\_%'
> GROUP BY meta_key
> ORDER BY meta_id DESC
> LIMIT 30
>
>
>
> Any ideas on how to optimize this query?  Or am I relying too much on
> postmeta?  It already has an index on the meta_key field.  I've seen
> this behavior on 2 different systems.  I have mysql query_cache_size
> turned on, so I would think this query would get cached, but it  
> doesn't
> appear to be.


--
Joseph Scott
joseph at josephscott.org
http://josephscott.org/