query-bench.php

<?php

$bench          = new Ubench();
$total          = 15000;
$count          = 10;

$sql = "
			SELECT l.ID, post_title, post_content, post_name, post_parent, post_author, post_modified_gmt, post_date, post_date_gmt
			FROM (
				SELECT {$wpdb->posts}.ID
				FROM {$wpdb->posts}
				WHERE {$wpdb->posts}.post_status = 'publish'
							AND {$wpdb->posts}.post_type = 'test'
							AND {$wpdb->posts}.post_password = ''
							AND {$wpdb->posts}.post_date != '0000-00-00 00:00:00'
				ORDER BY {$wpdb->posts}.post_modified ASC LIMIT %d OFFSET %d
			)
			o JOIN {$wpdb->posts} l ON l.ID = o.ID ORDER BY post_modified ASC
		";

$sql_no_opt = "
			SELECT ID, post_title, post_content, post_name, post_parent, post_author, post_modified_gmt, post_date, post_date_gmt
				FROM {$wpdb->posts}
				WHERE {$wpdb->posts}.post_status = 'publish'
							AND {$wpdb->posts}.post_type = 'test'
							AND {$wpdb->posts}.post_password = ''
							AND {$wpdb->posts}.post_date != '0000-00-00 00:00:00'
				ORDER BY {$wpdb->posts}.post_modified ASC LIMIT %d OFFSET %d
		";

echo '<pre>';
for ( $offset = 0; $offset <= $total; $offset += 1000 ) {

	$bench->start();
	$posts = $wpdb->get_results( $wpdb->prepare( $sql, $count, $offset ) );
	$bench->end();
	$optimized = rtrim( $bench->getTime(), 'ms' );

	$bench->start();
	$posts = $wpdb->get_results( $wpdb->prepare( $sql_no_opt, $count, $offset ) );
	$bench->end();
	$unoptimized = rtrim( $bench->getTime(), 'ms' );

	$difference = $optimized - $unoptimized;
	echo "For offset {$offset} query times are {$optimized}ms vs {$unoptimized}ms ({$difference}ms)\n";
}
echo '</pre>';