how to speed up a complex wp_query?

I made a WordPress plugin that checks if a bbPress forum / topic has been read.

Basically, when a topic is read, a post meta bbppu_read_by is added to the topic, which has for value the user ID.

Then, if I want to get the read topics (eg. here, for forum ID#3526 and user#1); I use a custom query with those args :

$read_query_args = array(
    'post_type'                 => 'topic',
    'post_parent'               => 3526,
    'posts_per_page'            => -1,
    'post_status'               => 'publish,closed,private',
    'meta_query' => array(
        //fetch only read posts
        array(
            'key'       => 'bbppu_read_by',
            'value'     => 1, //user ID
            'compare'   => '=',
        ),
        //ignore posts that are older than the user's registration time
        array(
            'key' => '_bbp_last_active_time',
            'value' => '2015-10-04 11:51:05',
            'compare' => '>',
        ),

    ),
    //try to optimize the query
    'fields'                    => 'ids', //get only IDs
    'no_found_rows'             => true, //https://wpartisan.me/tutorials/wordpress-database-queries-speed-sql_calc_found_rows
    'update_post_term_cache'    => false //ignore terms
);

That generates this query :

SELECT wp_posts.ID FROM wp_posts INNER JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id ) INNER JOIN wp_postmeta AS mt1 ON ( wp_posts.ID = mt1.post_id ) WHERE 1=1 AND wp_posts.post_parent = 3526 AND ( ( wp_postmeta.meta_key = '_bbp_last_active_time' AND wp_postmeta.meta_value > '2015-10-04 11:51:05' ) AND ( mt1.meta_key = 'bbppu_read_by' AND mt1.meta_value="1" ) ) AND wp_posts.post_type="topic" AND ((wp_posts.post_status="publish" OR wp_posts.post_status="closed" OR wp_posts.post_status="private")) GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC

In PhPMyAdmin, this query takes about 0.0393 seconds and returns 228 posts.

The same query without the postmeta stuff takes only 0.0022 seconds.

SELECT wp_posts.ID FROM wp_posts WHERE 1=1  AND wp_posts.post_parent = 3526 AND wp_posts.post_type="topic" AND ((wp_posts.post_status="publish" OR wp_posts.post_status="closed" OR wp_posts.post_status="private")) GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC 

This is a huge difference, it is about 18 times faster.

I have a user complaining about the plugin being slow; he has a forum with 100.000+ posts.

I was wondering if you had solutions to make this faster ?

What the user has read is a property of the user not of the topic. You need this information per users and for this you need to stored values in the user’s meta. You might also want to know which users read a topic so your current system is not mutually exclusive, but it is conceptually bad which results in it being slow.