php - Wordpress Ajax Custom Query - High CPU LOAD

admin管理员组

文章数量:1315850

I'm developing a fully AJAX community WP theme that comes with a lot of custom queries.

Everything is fine, there is no bug but recently but I had a few client that have a huge database and my theme started to kill CPU. I wonder how can I make it more faster.

What this code is doing? This code is trying to get posts that contains only have "comment" type comments (comment_type="comment") AND approved ones.

database information: Mysql - mariaDB

table name I'm trying to get result - wp_comments

how many data that table have - 380.000 (380K) comments

query's raw output time on phpmyadmin (w/o php) - 0.23 seconds

time to get this data on front end - 0.85-90 seconds

JS Code that calls function

    function lfload(page) {
  $("#sol-load").css("opacity","0.75");
  document.cookie = "lf= popular;  expires = Fri, 31 Dec 9999 23:59:59 GMT; path=/";
  document.cookie = "popular_page = " + page + ";  expires = Fri, 31 Dec 9999 23:59:59 GMT; path=/";
  var xhr = new XMLHttpRequest();
  xhr.open("POST", bilgi.tema_url + '/admin-ajax.php', true);
  xhr.setRequestHeader("Content-Type", "application/x-www-form-urlencoded");
  xhr.onreadystatechange = function() {
    if (this.readyState === XMLHttpRequest.DONE && this.status === 200) {
      document.getElementById("sol-load").innerHTML = this.response;
      $("#sol-load").css("opacity","1");
  }
  }
  xhr.send("action=popular_ajax");
}

PHP Code to get data

function popular_ajax()
  {
          global $wpdb;
           // getting latest page from cookie
          if (isset($_COOKIE["popular_page"]) AND 
          $_COOKIE["popular_page"] != 0) {
              $latest_sent_page_no = intval($_COOKIE["popular_page"]);
          } elseif (!isset($_COOKIE["popular_page"]) OR
           $_COOKIE["popular_page"] == 0) {
              $latest_sent_page_no = 1;
          }
          $page = $latest_sent_page_no;
          $cur_page = $page;
          $page -= 1;
          $per_page = 20;
          $start = $page * $per_page;
          $count_limiti = $per_page*50; 
          // tried to limit count 
          //query to make it more faster but did'nt work
          // count how many page exists to use it for pagination
          $count = $wpdb->get_var("SELECT COUNT(DISTINCT comment_post_ID)
           FROM ".$wpdb->prefix."comments
           USE INDEX (left_frame_index) 
           WHERE comment_approved = 1 
           AND comment_type = 'comment' 
           LIMIT $count_limiti");
          $sorgu = $wpdb->get_results("SELECT DISTINCT comment_post_ID 
            FROM ".$wpdb->prefix."comments 
          USE INDEX (left_frame_index) 
          WHERE comment_approved = 1
           AND comment_type = 'comment' 
           GROUP BY comment_post_ID 
           ORDER BY MAX(comment_date) 
           DESC LIMIT $start, $per_page");
          // GET DATA FROM SQL
          foreach($sorgu as $goflying2){
              // Çıktı bölgesi
              $msg .= "<li class='has-border-bottom'><a class='pr-0 pl-0 pt-1 
              pb-2 is-size-8'
               href='". get_permalink($goflying2->comment_post_ID) ."'
                title='".get_the_title($goflying2->comment_post_ID)."'>
                ".get_the_title($goflying2->comment_post_ID)."<span class='badge'>
                " .clean_comment_count_wo_newbies($goflying2->comment_post_ID). 
                "</span></a></li>";
          }
          wp_reset_postdata();
          // SAY THERE IS NO DATA IF ITS EMPTY
          if ($count == 0) {
            echo '<div class="tag has-text-centered has-fullwidth">
            <a class="pr-0 pl-0 pt-2 pb-2 is-size-8 has-text-dark">
            '.__("gündemimiz boş...", 'hype-community').'</a></div>';
              exit;
          }
          // PAGINATOIN STARTING HERE
          $no_of_paginations = ceil($count / $per_page);
          $start_loop = 1;
          $end_loop = $no_of_paginations;
          //conditional pagination
          if ($cur_page > 1) {
              $pre = $cur_page - 1;
              $pag_container .= "
              <button onclick='gundemNav(this)' value='$pre' class='button is-small'>
                <i class='fa fa-angle-left' aria-hidden='true'></i>
              </button>
              ";
          }
  
          $pag_container .= "
          <div class='dropdown is-hoverable has-fullwidth'>
        <button class='button is-small ml-3 mr-3 dropdown-trigger 
        has-fullwidth' aria-haspopup='true' aria-controls='dropdown-menu5'>
           <span>$cur_page</span>
           <span class='icon'>
    <i class='fa fa-caret-down'></i>
         </span>
            </button>
              ".'<div class="dropdown-menu" id="dropdown-menu5" role="menu">
      <div class="dropdown-content">';
      // tried to limit for loop
          if ($end_loop > 50) {
              $end_loop = 50;
          }
          for ($i = $start_loop; $i <= $end_loop; $i++) {
            // loop to print all page numbers
              if ($cur_page == $i) {
                  $pag_container .= "<a value='$i' class='dropdown-item 
                  is-active'>$i</a>";
              } else {
                  $pag_container .= "<a onclick='gundemNav(this)' value='$i'
                   class='dropdown-item'>$i</a>";
              }
          }
          $pag_container = $pag_container . "
  </div></div>
          </div>";
          if ($cur_page < $no_of_paginations) {
            //conditional pagination output
              $nex = $cur_page + 1;
              $pag_container .= "
              <button onclick='gundemNav(this)' value='$nex' class='button is-small'>
                <i class='fa fa-angle-right' aria-hidden='true'></i>
              </button>
              ";
          }
          if ($no_of_paginations == 1) {
            //conditional pagination output
              print     '<aside class="menu"><ul class="menu-list">' . $msg . 
              '</ul></aside>';
          } elseif ($cur_page == 1 and $no_of_paginations >= 2) {
            //conditional pagination output
              print     '<aside class="menu"><ul class="menu-list">' . $msg . 
              '</ul></aside>';
              echo  '
              <button onclick="gundemNav(this)" value="'. $nex .'" 
              class="button is-small is-bg-blue has-text-white is-fullwidth mt-3">
                <span class="icon">
                  <i class="fa fa-book" aria-hidden="true"></i>
                </span>
                <strong>'.__("fazlasını yükle", 'hype-community').'</strong>
              </button>
              ';
          } elseif ($cur_page != 1 and $no_of_paginations > 1) {
              //conditional pagination output
              echo
           '<div class="has-text-centered is-flex mb-3">'.$pag_container .
            '</div>'. // pagination
          '<aside class="menu"><ul class="menu-list">' . $msg . 
          '</ul></aside>'; // content that called from wpdb get results
          }
          // kill ajax
          exit;
  }

INDEX I used in this query

left_frame_index -> commenst_post_ID, comment_date, comment_approved, comment_type

How can I make it more faster, more stable. Is there something wrong with my SQL code or PHP code? This code is really killing the CPU...

Without this index, query is around 0.5 seconds (phpymyadmin exec. time. 1.5 seconds when calling with AJAX).

Thank you and sorry for my bad English! ^_^

本文标签： phpWordpress Ajax Custom QueryHigh CPU LOAD