2

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! ^_^

  • Additional information request. Any SSD or NVME devices on MySQL Host server? Post on pastebin.com and share the links. From your SSH login root, Text results of: B) SHOW GLOBAL STATUS; after minimum 24 hours UPTIME C) SHOW GLOBAL VARIABLES; D) SHOW FULL PROCESSLIST; AND Optional very helpful information, if available includes - htop OR top for most active apps, ulimit -a for a Linux/Unix list of limits, iostat -xm 5 3 for IOPS by device and core/cpu count, for server workload tuning analysis to provide suggestions. – Wilson Hauck Nov 17 at 20:35
  • There is no need to use custom SQL, WP_Query can order posts by their comment count, allowing you to take advantage of performance gains, e.g. priming post caches, fetching posts in bulk, or even object caching. All of that goes away because you used 3 raw SQL queries, and now any APIs you call based off of that data has to fetch the data in additional queries – Tom J Nowell Nov 24 at 0:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.