speed up pagination for huge database

Question

we have a wordpress with 1 million of product post and 15000 pagination page on archive , we have optimized all the website with good performance, but we have detected the pagination of archive is slow on wordpress, a example of query i have on archive page is like this:

SELECT SQL_CALC_FOUND_ROWS wp_posts.ID
FROM wp_posts 
WHERE 1=1 
AND wp_posts.post_type = 'prod_bb'
AND (wp_posts.post_status = 'publish'
OR wp_posts.post_status = 'private') 
ORDER BY ID
LIMIT 1043000, 70

this query is not optimized as to extrapolate 70 post makes an entire database scanning, After some research we found an interesting article on wordpress.com, how it is managed pagination

but trying to do some tests I can not run the code, this is test code i implemented:

<?php 
// Start with 0
$last_id = 0;

do {
    $blogs = $wpdb->get_results( $wpdb->prepare(
        "SELECT  wp_posts.ID
FROM wp_posts 
WHERE 1=1 
AND wp_posts.post_type = 'prod_bb'
AND (wp_posts.post_status = 'publish'
OR wp_posts.post_status = 'private') 
AND wp_posts.ID > %d LIMIT 70;",
        $last_id // Use the last ID to start after
    ) );

    foreach ( $blogs as $blog ) {
        // Do your thing!
  //  get_template_part( 'template-parts/content-index', get_post_format() );

        // ...
        // Record the last ID for the next loop
        $last_id = $blog->wp_posts.ID;
    }
// Do it until we have no more records
} while ( ! empty( $blogs ) );
 ?>

with this code the cpu of my server got to 100% , It remains in loading without showing anything

Answer 1

The article on wordpress is interesting and true but I'm not sure what's it's purpose. The provided code is for cases when you want to do some action for every post ; it doesn't help with the pagination on site directly.

You may try https://wpartisan.me/tutorials/wordpress-database-queries-speed-sql_calc_found_rows instead, but with million posts, even better would be to completely rethink your pagination ; the "LIMIT 1043000, 70" is very inefficient part.