Extremely inefficient but easily fixable query in News SEO

[archon810]

There's a query in WP SEO here https://github.com/Yoast/wpseo-news/blob/trunk/classes/sitemap.php#L233 that looks like this:

		// Get posts for the last two days only, credit to Alex Moss for this code.
		$items = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT ID, post_content, post_name, post_author, post_parent, post_date_gmt, post_date, post_date_gmt, post_title, post_type
				FROM {$wpdb->posts}
				WHERE post_status='publish'
					AND ( TIMESTAMPDIFF( MINUTE, post_date_gmt, UTC_TIMESTAMP() ) <= ( 48 * 60 ) )
					AND post_type IN (" . implode( ',', array_fill( 0, count( $post_types ), '%s' ) ) . ')
				ORDER BY post_date_gmt DESC
				LIMIT 0, %d
				',
				$replacements
			)
		);

The problem with this query is it is extremely slow due to TIMESTAMPDIFF and UTC_TIMESTAMP calculations. I'm talking a full minute for a table of 700k wp_posts rows. It's probably a lot worse with busier dbs.

But this can be easily fixed with a slight tweak - and this query runs in less than 0.1s. Replace TIMESTAMPDIFF( MINUTE, post_date_gmt, UTC_TIMESTAMP() ) <= ( 48 * 60 ) with post_date_gmt >= NOW() - INTERVAL 48 HOUR and voila. Same results, but at 1000x the speed.

Please consider pushing this change out ASAP after your tests.

Thank you.