[wp-hackers] sql problems
yahgrp at poplarware.com
Tue Apr 21 15:26:57 GMT 2009
Regarding those two queries:
1) The "ratings" table is from a plugin, not the core of WordPress, so
you probably ought to take that up with the plugin author.
2) I did a little searching through the code to investigate where this
query could be coming from.
- The query itself is apparently generated in function
update_postmeta_cache(), in file wp-includes/post.php It looks like it
at least caches the data it finds, so it shouldn't repeat itself on
any particular post ID.
- That function update_postmeta_cache() is called with multiple IDs
passed in in exactly one place: from function update_post_caches() in
the same file.
- It looks like the only call to update_postmeta_cache() comes from
WP_Query->get_posts(), which is the central WP query. So every time
you query to get posts (i.e. every page load), it will pass in the
list of all the IDs it found in the query, and cache their meta-data.
So I'm not sure why you have so many post IDs in your queries, but I
guess the postmeta cache function's query was written that way because
it assumed you would use something like the default of 10 posts per
page, and 10 posts per RSS feed, which would mean only 10 post IDs in
the IN(...) part of the query. But you are right, it would probably be
better to do something different, because some users might display
more posts per page.
Another question is whether the post meta data needs to be retrieved
in every case. I personally never use post meta, so why would I want
to retrieve it and cache it at all? Maybe something more efficient
could be done, like retrieving it only when someone calls the template
tags that need it? Perhaps a Trac ticket would be in order...
B. V. wrote:
> There are some problems with MySQL optimizations ( on big sites, are
> visibile effects ).
>>From my mysql-slow-query log:
> 1. *SELECT rating_username, rating_rating, rating_ip FROM wp_ratings WHERE
> rating_postid = 450;*
> 2. in wp-includes/post.php it's a strange query ( see bellow ) in function
> update_postmeta_cache(), reported by mysql-slow-query log:
> *SELECT post_id, meta_key, meta_value FROM wp_postmeta WHERE post_id IN
> This query apears on about each 5 seconds ...
> This I don't know how to fix it, only to offer my suggestion that there
> should be a table ( temporary or not ) for those values search with mysql
> "SELECT ... WHERE post_id IN (x,y,z,..... )" !
Jennifer Hodgdon * Poplar ProductivityWare
Drupal, WordPress, and custom Web programming
More information about the wp-hackers