AnswerBun.com

Wordpress Ajax Custom Query - High CPU LOAD

WordPress Development Asked by techn9ne on December 22, 2020

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

Add your own answers!

Related Questions

CREATE TABLE with dbDelta does not create table

1  Asked on October 30, 2021 by fcd

     

how to add shortcode widget at end of main blog page

1  Asked on October 30, 2021 by shubhamk

 

Placing background image over section divider

1  Asked on October 30, 2021 by user191925

   

jQuery Click event is not fired at all

1  Asked on October 30, 2021 by islam-hanafi

   

Make the product page as homepage WooCommerce

0  Asked on October 30, 2021 by gabriel-borges

     

How to be sure to get last page version without ANY cache?

2  Asked on October 30, 2021 by j-bizmai

   

How to grab posts in Gutenberg Block?

1  Asked on October 30, 2021 by stephen-sabatini

 

Custom post type permalink returns bad url

1  Asked on October 30, 2021 by ncti

     

Menus don’t appear in admin area / WordPress Multisite

0  Asked on October 30, 2021 by user191642

   

Client side HTTP parameter pollution (reflected)

0  Asked on October 30, 2021 by divided

 

WP Ajax Function Always Returning undefined

1  Asked on October 30, 2021 by brian-bruman

     

Group Posts by Category in Monthly Archive

2  Asked on October 30, 2021 by zero-goki

 

Ask a Question

Get help from others!

© 2022 AnswerBun.com. All rights reserved. Sites we Love: PCI Database, MenuIva, UKBizDB, Menu Kuliner, Sharing RPP