b0b_

EDIT: after this workaround, pagination doesn’t seem to work.
To fix the issue simply add a count query like this:

add_filter('pre_get_posts', 'optimized_get_posts', 100);
function optimized_get_posts() {
	global $wp_query, $wpdb;

	$wp_query->query_vars['no_found_rows'] = 1;
	$wp_query->found_posts = $wpdb->get_var( "SELECT COUNT(*) FROM wp_posts WHERE 1=1 AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'private')" );
	$wp_query->found_posts = apply_filters_ref_array( 'found_posts', array( $wp_query->found_posts, &$wp_query ) );
	$wp_query->max_num_pages = ceil($wp_query->found_posts / $wp_query->query_vars['posts_per_page']);

 	return $wp_query;
}

In fact, when you set the no_found_rows to 1, wp core doesn’t calculate the total posts number, and for that, the pagination results broken.

Setting proper query_vars to the correct count(*) value (that is more effective and “speedy” than SQL_CALC_FOUND_ROWS), fixes the issue.

Cheers (again)

Ok. After a few days of googling and trials, i finally figured how to fix SQL_CALC_FOUND_ROWS without changing wp core functions.

The only part of wp core in which occurs SQL_CALC_FOUND_ROWS, is line 2603 of the module query.php. Using a filter on wp_query (inserted in a function module or in a plugin), called on pre_get_posts hook, you can avoid query function to access the part of code in which $found_rows is set.

That’all. Clean, effective and simple.

add_filter('pre_get_posts', 'optimized_get_posts', 100);
function optimized_get_posts() {
	global $wp_query;
	$wp_query->query_vars['no_found_rows'] = 1;
 	return $wp_query;
}

The query now takes about 0.015 secs (instead of 0.56 secs before).

Let me know if this works for you too.
Cheers