Show only one post for each author ( Page loads too slow )

Question

I want to show only one latest post by each author and found this code that it working. However, it takes too long to load the page since I have over 100K authors and 100K posts in database. How can I optimize this code?

<?php
  //Displaying latest post per author on front page
  function filter_where($where = '') {
    global $wpdb;

    $where .= " AND wp_posts.id = (select id from {$wpdb->prefix}posts p2 where p2.post_status = 'publish' and p2.post_author = {$wpdb->prefix}posts.post_author order by p2.post_date desc limit 0,1)";
    return $where;
  }
  add_filter('posts_where', 'filter_where');
  query_posts($query_string);

//The Loop
if ( have_posts() ) : while ( have_posts() ) : the_post();

...
  ?>

Here is an original article for the code.

http://www.dbuggr.com/smallwei/show-latest-post-author-wordpress-front-page/

Answer 1

Edit: I just tested and it works perfectly. You'll also want to remove pagination from the author template.

I haven't tested it, but have you tried pre_get_posts?

function author_filter($query) {
if ( !is_admin() && $query->is_main_query() ) {
  if ($query->is_author()) {
    $query->set( 'posts_per_page', 1 );
   }
 }
}
add_action('pre_get_posts','author_filter');

Answer 2

Wow! A subquery in the WHERE clause! That is a nasty one. :)

I can't test this right now (on the phone, this all memory) but let me see what I can do. I think that all you need is a GROUP_BY filter:

function group_by_author($groupby) {
  global $wpdb;
  return " {$wpdb->posts}.post_author";
}
add_filter('posts_groupby','group_by_author');

Then create a new Wp_Query with other relevant arguments -- not query_posts! Not ever!

Proof of concept:

function group_by_author($groupby) {
  global $wpdb;
  return " {$wpdb->posts}.post_author";
}
add_filter('posts_groupby','group_by_author');

$args = array(
  'post_type' => 'post',
  'posts_per_page' => -1,
  'ignore_stickie_posts' => true,
  'order' => 'ASC',
);
$q = new WP_Query($args);
var_dump($q);
if ($q->have_posts()) {
  while ($q->have_posts()) {
    $q->the_post();
    the_title();
    the_author();
    echo '<br>';
  }
}