wordpress为百万+的帖子，如何优化它

Question

i want try to store on a wordpress site more 1 million of post to check if it's can do that, so have created a random post similar to the post we want post on this website, after import 1000000+ post the result is :

the main query of archive with 70 post (index.php) require around 1,60 second

so i started a debug of query and this is the standard query :

SELECT SQL_CALC_FOUND_ROWS wp_posts.ID
FROM wp_posts 
WHERE 1=1 
AND wp_posts.post_type = 'post'
AND (wp_posts.post_status = 'publish'
OR wp_posts.post_status = 'private') 
ORDER BY wp_posts.post_date DESC
LIMIT 1035930, 70

so i started to manipulate this query for check if i can optimize it with my requirement, after this we have checked a query like this require only 0,40 second

SELECT SQL_CALC_FOUND_ROWS wp_posts.ID
FROM wp_posts 
WHERE 1=1 
AND wp_posts.post_type = 'post'
LIMIT 1035930, 70

with this trick:

• if pass the ORDER BY wp_posts.post_date DESCto ORDER BY IDthe query require 1,20 second (0, 40 second earned)

• if remove ORDER BY the query require 1 second (0,20 second earned)

• if remove AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'private') the query require 0,40 second (0,60 second earned)

so the huge condition is post_status and ORDER BY

on my project in not necessary the "post_status" so i can delete it ?.

ORDER BY, instead to use a mysql can replace it in php? I learned php is more faster, right?

so how I can still improve this query ?

then how i can replace the main query with the fastest query, example i tried to use this code for remove order by

add_filter ('posts_orderby', 'bm_featureHomeFilterOrder');

    function bm_featureHomeFilterOrder ($order = '') {
        global $wpdb;
        $order = 'none';
        return $order;
    }

but not work with "none", work only with other variable like ID-title etc

can use a native mysql query with pre_get_post for remove it ?

like :

 $query = $wpdb->get_results( "SELECT SQL_CALC_FOUND_ROWS wp_posts.ID
FROM $wpdb -> wp_posts 
WHERE 1=1 
AND wp_posts.post_type = 'post'
LIMIT 12080, 70" );

the website is hosted with this configuration:

• php 7 FPM
• madiadb
• memcached (database cache and object cache)
• APC
• NGINX
• tuned mariadb configuration with mysqltuner.

this configuration is made by easyengine script.

the theme in use is starter theme https://underscores.me/ check the code for more info for index.php page

the data retrieved in archive is ( only featured image + title ).

the data retrieved in single post is ( title, categories, content, 25 related post ).

Answer 1

LIMIT 1035930, 70 is a performance killer. It must scan 1035930 + 70 rows to get the 70 you desire.

If you are simply walking through the table, here are two documents to read:

• evils of OFFSET Hint: "remember where you left off"
• chunking your way through a table

Unfortunately, SQL_CALC_FOUND_ROWS requires completing the entire scan, which is probably a million-row scan in your case. You should really think twice about whether it is worth finding the exact count. After all, search engines first went to "over 123,000 hits", then abandoned the 'useless' number all together.

If, after you have implemented my suggestions, you still have trouble, start a new Question with your revised queries, SHOW CREATE TABLE and EXPLAIN SELECT .... (I have many rants about the WP schema, some may apply to your use case.) WP was designed for casual usage, and needs to help to get to million-row usage.