[wp-hackers] optimizing get_posts

[Komra Moriko]

I have one slow query left in my installation, it is used on paged  
post listings and comes from the get_posts function. Sorry if this  
has already been hashed out in the past...

SELECT DISTINCT * FROM wp_posts  WHERE 1=1 AND post_date_gmt <=  
'2006-12-01 10:24:59' AND (post_status = "publish") AND post_status ! 
= "attachment" GROUP BY  wp_posts.ID  ORDER BY post_date DESC LIMIT  
0, 25;

Given the structure of this query, DISTINCT and GROUP BY are  
unnecessary. Adding DISTINCT causes the use of a temporary table, and  
adding GROUP BY incurs Using filesort. The following would be a more  
ideal query, and with an index OF (post_status,post_date) it becomes  
very efficient:

SELECT DISTINCT * FROM wp_posts  WHERE 1=1 AND post_date <=  
'2006-12-01 10:24:59' AND (post_status = "publish") AND post_status ! 
= "attachment" GROUP BY  wp_posts.ID  ORDER BY post_date DESC LIMIT  
0, 25;


Obviously the get_posts function allows for many contingencies. It  
would make sense to only have DISTINCT and/or GROUP BY if there was a  
join. Would it be safe to modify this function to construct the query  
string with the $distinct and $group by variables included only when  
$join is set (or non-empty)?

komra

P.S. Is there any reason for the use of post_date_gmt in the original  
query as opposed to post_date?


Komra Moriko
http://www.design4results.com
http://www.marketingvox.com (now powered by WP)
http://www.mediabuyerplanner.com (now powered by WP)