The paging is very slow under a large number of articles.

  • If I have 500,000 articles, the pagination will be very slow.
    I tried this method, but it didn’t work much. The query is still very slow.

    Is there any way to improve this problem?

    SELECT   wp_posts.ID FROM wp_posts  LEFT JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id) WHERE 1=1  AND ( 
  wp_term_relationships.term_taxonomy_id IN (7,234,580,665,854,1053,1080,1444,6562,6563)
) AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish' OR wp_posts.post_author = 94179 AND wp_posts.post_status = 'private') GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC LIMIT 1260, 20;

#Query_time: 12.81016

    The page I need help with: [log in to see the link]

Viewing 8 replies - 1 through 8 (of 8 total)
  • Moderator James Huff

    (@macmanx)

    Here are some general recommendations for speeding things up: https://codex.www.ads-software.com/WordPress_Optimization

    Thread Starter fantasts

    (@fantasts)

    @macmanx
    I can do as much as I can.
    This is undoubtedly a flaw in WordPress, using the most primitive pagination method.

    Moderator James Huff

    (@macmanx)

    There are many large sites on WordPress without this issue: https://www.ads-software.com/showcase/tag/news/

    So, I encourage you to try what you can following the guide: https://codex.www.ads-software.com/WordPress_Optimization

    Thread Starter fantasts

    (@fantasts)

    @macmanx
    I am a programmer and I have tried the methods mentioned in the guide.
    I think this is a flaw in WordPress. I am tired of constantly optimizing the wordpress database. I hope that future version updates will be resolved.
    At the moment, I can only solve this problem by redesigning the pagination method.

    Moderator James Huff

    (@macmanx)

    What version of PHP and MySQL are you running?

    Thread Starter fantasts

    (@fantasts)

    @macmanx

    server configuration
    Processor E5-2670 @ 2.60GHz *2
    Ram 32 GiB

    mysql Ver 15.1 Distrib 10.3.7-MariaDB, for Linux (x86_64) using readline 5.1
    PHP 7.2.7
    wordpress 5.0.3

    This is a very obvious problem, you only need to test it to get the answer.
    WordPress is still using the most primitive paging method, which is extremely inefficient.

    • This reply was modified 5 years, 8 months ago by fantasts.
    Moderator James Huff

    (@macmanx)

    Any improvement under WordPress 5.1.1?

    I don’t have a site with 500,000 posts myself, but I don’t see any pagination-related speed issues at these WordPress sites:

    https://time.com/
    https://fivethirtyeight.com/
    https://techcrunch.com/
    https://www.vogue.com/
    https://variety.com/

    Thread Starter fantasts

    (@fantasts)

    No changes, I have tested it in the experimental environment.
    The website you listed cleverly uses the count calculations that were avoided on the previous and next pages. And optimized the wordpress database query.
    You may not be a technician, you can see a simple example.

    Select * from table limit 400000, 20
    It takes 3.229 seconds
    Select * from table limit 800000, 20
    It took 37.44 seconds

    The above is the paging method currently used by wordpress.
    Let’s change the way we write it and optimize it.

    SELECT * FROM table WHERE ID > =(select id from product limit 800000, 1) limit 20
    It takes 0.2 seconds
    SELECT * FROM table WHERE ID BETWEEN 800000 AND 800020;
    It takes 0.1 seconds

    The gap is so obvious! I think wordpress has to do something, I am tired of constantly optimizing wordpress.

    • This reply was modified 5 years, 8 months ago by fantasts.
Viewing 8 replies - 1 through 8 (of 8 total)
  • The topic ‘The paging is very slow under a large number of articles.’ is closed to new replies.