[wp-hackers] Optimize Post Meta Query

[Dan Fellars]

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 ):

 

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.

 

 

Thanks,

Dan