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 clients that have a huge database and my theme started to kill the CPU. I wonder how can I make it run 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 = 9999年12月31日 23:59:59 GMT; path=/";
document.cookie = "popular_page = " + page + "; expires = 9999年12月31日 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 run faster, be 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).
sorry for my bad English! ^_^
2 Answers 2
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_limit
Needs a better index; see the below.
Do NOT compare a VARCHAR (such as comment_approved) with a numeric literal (such as 1), the index cannot be used. Put quotes around "1" or '1'.
Get rid of the "index hint"; it may be hurting more than helping.
Why no ORDER BY? A limit without an order-by give you "random" rows.
Oops, the real problem is that the query generates only 1 row, so there is no use in having a LIMIT. Keep the DISTINCT; toss the LIMIT.
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
See above, plus
Don't mix GROUP BY and DISTINCT. Use only the GROUP BY.
This query needs a different index (use the specified order):
INDEX(comment_type, comment_approved, comment_post_ID, comment_date)
I suspect this query is not giving you what you expect? Please describe in English what the goal is.
Pagination via OFFSET has problems. See http://mysql.rjweb.org/doc.php/pagination
Overall comments
Overall I’d say that the practice of sending HTML in an AJAX response and using that to directly set the content of a DOM element is an antiquated practice, and could be an XSS avenue. While the practice of sending data from the API and having the front end code construct the HTML dynamically may have been a new construct eight years ago it is much more common in today’s web.
Improving speed of code to lookup posts
To answer your question "How can I make it more faster, more stable." it looks like there is a query to get IDs for each post, and then in the loop to create list items, there are calls to get_permalink(), get_the_title() and clean_comment_count_wo_newbies(). Do those function calls run queries against the database? If so, that would likely be the bottleneck. Ideally the code would run a single query to get all the information needed - e.g. IDs, titles, comment counts, etc. Remember that DB queries are expensive so it is best to minimize the number of queries needed.
JavaScript
DOM Access
The code looks up the element with id sol-load three times within a few lines. It would be wise to cache those lookups, since they can be expensive.
"...DOM access is actually pretty costly - I think of it like if I have a bridge - like two pieces of land with a toll bridge, and the JavaScript engine is on one side, and the DOM is on the other, and every time I want to access the DOM from the JavaScript engine, I have to pay that toll"
- John Hrvatin, Microsoft, MIX09, in this talk Building High Performance Web Applications and Sites at 29:38, also cited in the O'Reilly Javascript book by Nicholas C Zakas Pg 36, as well as mentioned in this post
const solLoad = $("#sol-load");
AJAX
The front end code uses jQuery to access DOM elements yet the AJAX code uses vanilla XHR mechanisms. The jQuery AJAX methods like $.get() and $.post() could be used to simplify the code. Actually .load() can be used to simplify the code dramatically -
I haven't tested this but this should be what would be needed:
solLoad.load(bilgi.tema_url + '/admin-ajax.php', {action: 'popular_ajax'},
solLoad.css.bind(solLoad, "opacity", 1));
Inline event handlers
There are event handlers registered within the HTML code - e.g.
<button onclick='gundemNav(this)'
It is better to register event handlers within the JavaScript (e.g. using button.addEventListener (can be done when element is created or after is is selected via DOM) for multiple reasons:
- The logic can be separated from the markup - if multiple teammates worked on the project then one could work on the JavaScript while the other could work on the HTML independently.
- Such handlers can pollute the global namespace which can lead to strange behavior.
Explore related questions
See similar questions with these tags.
DISTINCT comment_post_IDandGROUP BY comment_post_IDin your query. Either one will be enough. I doubt this will affect CPU usage, but it is worth testing which variant is performs best. See also: stackoverflow.com/questions/581521/… \$\endgroup\$