iphone/ipad/android – mobile developer blog

if you want to get a solution for the slow log on wordpress installations with many posts (more than 10k), then you should do the following: in wp-includes/query.php is something like:

$found_rows = '';
if ( !$q['no_found_rows'] && !empty($limits) )
    $found_rows = 'SQL_CALC_FOUND_ROWS';

after this you should insert:

$tmp = " SELECT $distinct $wpdb->posts.id FROM $wpdb->posts $join
WHERE 1=1 $where $groupby $orderby $limits";

$tmp_q = $wpdb->get_col($tmp);
$tmp_ids = implode($tmp_q,',');
$sqlString = " SELECT $distinct $fields FROM $wpdb->posts $join
WHERE $wpdb->posts.id IN ($tmp_ids) $groupby $orderby ";

$sqlString2 = " SELECT $found_rows $distinct $fields
FROM $wpdb->posts $join
WHERE 1=1 $where $groupby $orderby $limits";

$this->request = $sqlString;

after the code above should follow (nothing should be inbetween):

if ( !$q['suppress_filters'] )
    $this->request = apply_filters_ref_array('posts_request',
array( $this->request, &$this ) );

now, after some lines there is:

if ( !$q['no_found_rows'] && !empty($limits) ) {
    $found_posts_query = apply_filters_ref_array( 'found_posts_query',
array( 'SELECT FOUND_ROWS()', &$this ) );

the second line should be changed to:

    $found_posts_query = apply_filters_ref_array( 'found_posts_query',
array( "SELECT count($distinct $wpdb->posts.id)
FROM $wpdb->posts $join WHERE 1=1 $where", &$this ) );

watch out when you are doing an update!!!!