WordPress.org

Ready to get started?Download WordPress

Forums

WordPress need to be optimized for big database (1 post)

  1. nymyogi
    Member
    Posted 6 years ago #

    I have problem with my wordpress. I have 2 wordpress with 17.000 post and 300.000 post.

    My hosting company disable it since the load to database is very big.

    I tried to examine the log, and found that wordpress need to optimized for relationship taxonomy and archive calendar

    Summarizing data for calendar based archive which display the summary data for each day in a month using calendar.

    Most likely other archive summary query also take big database server resources.

    Hope this is can be one of consideration for future wordpress development.

    Regards
    Yogi Triana.

    Following are the process list of my database server
    mysql> show processlist;
    +----------+-----------------+-------------------------------------------
    --------------+-------------------+----------------+------+--------------
    --------+----------------------------------------------------------------
    --------------------------------------+
    | Id | User | Host
    | db | Command | Time | State
    | Info
    |
    +----------+-----------------+-------------------------------------------
    --------------+-------------------+----------------+------+--------------
    --------+----------------------------------------------------------------
    --------------------------------------+
    | 16794704 | topklik | krusty.dreamhost.com:59080
    | lyrics_topklik | Query | 3880 | Sending data
    | SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts INNER JOIN
    wp_term_relationships ON (wp_posts. |
    | 16796019 | topklik | krusty.dreamhost.com:54888
    | lyrics_topklik | Query | 3124 | Sending data
    | SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts INNER JOIN
    wp_term_relationships ON (wp_posts. |
    | 16796037 | topklik | krusty.dreamhost.com:55935
    | lyrics_topklik | Query | 3111 | Sending data
    | SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts INNER JOIN
    wp_term_relationships ON (wp_posts. |
    | 16796636 | topklik | krusty.dreamhost.com:47721
    | lyrics_topklik | Query | 2735 | Sending data
    | SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts INNER JOIN
    wp_term_relationships ON (wp_posts. |
    | 16799376 | topklik | krusty.dreamhost.com:33864
    | lyrics_topklik | Query | 1069 | Sending data
    | SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts INNER JOIN
    wp_term_relationships ON (wp_posts. |
    | 16799793 | topklik | krusty.dreamhost.com:36405
    | lyrics_topklik | Query | 861 | Sending data
    | SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts INNER JOIN
    wp_term_relationships ON (wp_posts. |
    | 16799899 | topklik | krusty.dreamhost.com:60685
    | lyrics_topklik | Query | 800 | Sending data
    | SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts WHERE 1=1
    AND post_type = 'post' AND (post_st |
    | 16799900 | topklik | krusty.dreamhost.com:45394
    | lyrics_topklik | Query | 800 | Sending data
    | SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts WHERE 1=1
    AND post_type = 'post' AND (post_st |
    | 16799933 | topklik | krusty.dreamhost.com:38855
    | lyrics_topklik | Query | 770 | Sending data
    | SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts WHERE 1=1
    AND post_type = 'post' AND (post_st |
    | 16800176 | topklik | krusty.dreamhost.com:33143
    | lyrics_topklik | Query | 636 | Sending data
    | SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts WHERE 1=1
    AND post_type = 'post' AND (post_st |
    | 16800289 | topklik | krusty.dreamhost.com:59174
    | lyrics_topklik | Query | 573 | Sending data
    | SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts WHERE 1=1
    AND post_type = 'post' AND (post_st |
    | 16800433 | topklik | krusty.dreamhost.com:43778
    | lyrics_topklik | Query | 493 | Sending data
    | SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts WHERE 1=1
    AND post_type = 'post' AND (post_st |
    | 16800444 | topklik | krusty.dreamhost.com:36335
    | lyrics_topklik | Query | 488 | Copying to tmp
    table | SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts INNER JOIN
    wp_term_relationships ON (wp_posts. |
    | 16800645 | topklik | krusty.dreamhost.com:55609
    | lyrics_topklik | Query | 370 | Sending data
    | SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts WHERE 1=1
    AND post_type = 'post' AND (post_st |
    | 16800780 | topklik | krusty.dreamhost.com:46100
    | lyrics_topklik | Query | 307 | Copying to tmp
    table | SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts INNER JOIN
    wp_term_relationships ON (wp_posts. |
    | 16800787 | topklik | krusty.dreamhost.com:40288
    | lyrics_topklik | Query | 303 | Sending data
    | SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts WHERE 1=1
    AND post_type = 'post' AND (post_st |
    | 16801009 | topklik | krusty.dreamhost.com:42255
    | lyrics_topklik | Query | 184 | Sending data
    | SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts WHERE 1=1
    AND post_type = 'post' AND (post_st |
    | 16801039 | jobsdb | krusty.dreamhost.com:39194
    | jobsdb | Query | 167 | Sending data
    | SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts WHERE 1=1
    AND YEAR(post_date)='2007' AND MONT |
    | 16801091 | jobsdb | krusty.dreamhost.com:40689
    | jobsdb | Query | 147 | Sending data
    | SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts WHERE 1=1
    AND YEAR(post_date)='2007' AND MONT |
    | 16801120 | topklik | krusty.dreamhost.com:55974
    | lyrics_topklik | Query | 59 | Sending data
    | SELECT p.ID, p.post_title FROM wp_posts AS p WHERE p.post_date <
    '2008-01-23 16:32:03' AND p.post_t |
    | 16801174 | topklik | krusty.dreamhost.com:44284
    | lyrics_topklik | Query | 60 | Sending data
    | SELECT COUNT(*) FROM wp_term_relationships, wp_posts WHERE
    wp_posts.ID = wp_term_relationships.objec |
    | 16801186 | jobsdb | krusty.dreamhost.com:45587
    | jobsdb | Query | 90 | Sending data
    | SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts WHERE 1=1
    AND YEAR(post_date)='2007' AND MONT |
    | 16801220 | jobsdb | krusty.dreamhost.com:43156
    | jobsdb | Query | 72 | Sending data
    | SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts WHERE 1=1
    AND (post_type = 'post' AND (post_s |
    | 16801244 | jobsdb | krusty.dreamhost.com:36103
    | jobsdb | Query | 60 | Sending data
    | SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts WHERE 1=1
    AND YEAR(post_date)='2007' AND MONT |
    | 16801245 | topklik | krusty.dreamhost.com:42277
    | lyrics_topklik | Query | 59 | Sending data
    | SELECT p.ID, p.post_title FROM wp_posts AS p WHERE p.post_date <
    '2008-01-23 16:32:03' AND p.post_t |
    | 16801262 | topklik | krusty.dreamhost.com:45339
    | lyrics_topklik | Query | 52 | Sorting result
    | SELECT post_modified_gmt FROM wp_posts WHERE post_status =
    'publish' ORDER BY post_modified_gmt DESC |
    | 16801275 | jobsdb | krusty.dreamhost.com:45363
    | jobsdb | Query | 23 | Sending data
    | SELECT post_id, category_id FROM wp_post2cat LEFT JOIN wp_posts
    ON post_id = ID WHERE category_id IN |
    | 16801278 | topklik | krusty.dreamhost.com:36482
    | lyrics_topklik | Query | 31 | Sending data
    | SELECT COUNT(*) FROM wp_term_relationships, wp_posts WHERE
    wp_posts.ID = wp_term_relationships.objec |
    | 16801280 | topklik | krusty.dreamhost.com:36925
    | lyrics_topklik | Sleep | 32 |
    | NULL
    |
    | 16801286 | topklik | krusty.dreamhost.com:33726
    | lyrics_topklik | Query | 23 | Sending data
    | SELECT p.ID, p.post_title FROM wp_posts AS p WHERE p.post_date <
    '2008-01-23 16:32:03' AND p.post_t |
    | 16801287 | jobsdb | krusty.dreamhost.com:40214
    | jobsdb | Query | 29 | Sending data
    | SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts WHERE 1=1
    AND (post_type = 'post' AND (post_s |
    | 16801305 | topklik | krusty.dreamhost.com:59765
    | lyrics_topklik | Query | 3 | Sending data
    | SELECT p.ID, p.post_title FROM wp_posts AS p WHERE p.post_date <
    '2008-01-23 16:32:03' AND p.post_t |
    +----------+-----------------+-------------------------------------------
    --------------+-------------------+----------------+------+--------------
    --------+----------------------------------------------------------------
    --------------------------------------+

Topic Closed

This topic has been closed to new replies.

About this Topic