[wp-hackers] sql problems

[Jennifer Hodgdon]

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...

     --Jennifer

B. V. wrote:
> Hi.
> 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
> (866,862,860,858,856,854,852,850,848,846,844,842,839,837,835,833,831,829,827,825,823,821,819,817,815,813,810,808,806,804,802,800,798,795,793,790,788,786,784,782,780,778,776,774,772,770,767,765,763,761,759,701,698,696,694,692,690,688,686,684,682,680,678,676,674,672,670,668,666,664,662,660,658,655,653,651,649,533,531,529,527,524,521,519,516,514,512,510,508,506,504,502,500,498,496,494,492,490,486,484,482,480,478,476,473,470,468,466,464,463,458,460,457,452,451,447,448,444,442,441,438,429,435,433,431,427,424,425,421,420,418,414,413,406,410,408,405,402,398,400,396,393,391,389,388,384,385,379,376,374,372,371,368,366,365,357,361,359,356,353,351,350,346,347,344,342,339,336,337,334,331,328,326,324,321,322,319,316,314,313,309,307,306,302,303,299,293,288,290,286,284,283,280,275,278,276,273,270,268,265,259,263,258,255,218,1);
> *
> 
> 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
www.poplarware.com
Drupal, WordPress, and custom Web programming