How To Manually Optimize Slow Database Queries Causing Resource Usage Surge?

Hi Everyone,

This is my first post here after pulling my hair for several months on my site’s resource overusage. I never exer

Siteground advised me to fix the following top 5 slow queries.

=== TOP 5 of 5 (total) Slow Queries for the past 24 hours ==========
1. Executed 1h 49m 40s ago for 5.146361 sec on Database --> akoph596_wp624
Date: 2017-08-17 14:02:25 Query_time: 5.146361 Rows_examined: 3: Rows_sent 1 Lock_time: 0.005953
# Schema: akoph596_wp624 Last_errno: 0 Killed: 0 SELECT t.*, tt.* FROM wpjd_terms AS t INNER JOIN wpjd_term_taxonomy AS tt ON t.term_id = tt.term_id WHERE tt.taxonomy IN ('category') AND t.slug = 'entertainment' ORDER BY t.name ASC;
------------------------------------------------------------------------------------------------
2. Executed 18h 52m 0s ago for 2.894769 sec on Database --> akoph596_wp624
Date: 2017-08-16 21:00:05 Query_time: 2.894769 Rows_examined: 5762: Rows_sent 783 Lock_time: 0.004616
# Schema: akoph596_wp624 Last_errno: 0 Killed: 0 SELECT option_name, option_value FROM wpjd_options WHERE autoload = 'yes';
-----------------------------------------------------------------------------------------------------
3. Executed 2h 52m 2s ago for 2.033552 sec on Database --> akoph596_wp624
Date: 2017-08-17 13:00:03 Query_time: 2.033552 Rows_examined: 5222: Rows_sent 780 Lock_time: 0.000480
# Schema: akoph596_wp624 Last_errno: 0 Killed: 0 SELECT option_name, option_value FROM wpjd_options WHERE autoload = 'yes';
-----------------------------------------------------------------------------------------------------
4. Executed 12h 39m 10s ago for 1.539306 sec on Database --> akoph596_wp624
Date: 2017-08-17 03:12:55 Query_time: 1.539306 Rows_examined: 1529: Rows_sent 1 Lock_time: 0.000124
# Schema: akoph596_wp624 Last_errno: 0 Killed: 0 SELECT post_modified_gmt FROM wpjd_posts WHERE post_status = 'publish' AND post_type IN ('post', 'page', 'attachment') ORDER BY post_modified_gmt DESC LIMIT 1;
---------------------------------------------------------------------------------------------------
5. Executed 2h 51m 58s ago for 1.241616 sec on Database --> Unknown (NOT FOUND / DELETED)
Date: 2017-08-17 13:00:07 Query_time: 1.241616 Rows_examined: 5224: Rows_sent 781 Lock_time: 0.000460
# Schema: akoph596_wp624 Last_errno: 0 Killed: 0 SELECT option_name, option_value FROM wpjd_options WHERE autoload = 'yes';

How can I go about fixing these queries. I have used WP-Optimize plugin to optimize the database of the site but I guess I need to do this manually?

I’m using Newspaper theme 8.1 with their your new composer plugin instead of the previous composer visual composer.

Thank you very much for your help.

Best regards,

Aldin

Couple suggestions
a) make sure running php 5.6 and up with opcache on

b) limit number of queries per page; meaning don’t “turn everything on…” — you might want to only have a max of two ‘modules’ on sidebar, turn off the related posts widget and use simpler plugin, do NOT use the mega menu with images/posts, don’t use ‘7 days most popular’ option, don’t use the fly out related ‘you might like…’

Every one of those blocks is a dbase set of queries, so if you have 50 queries per page, cutting that down to 20 speeds everything up.

Also, if you’re getting ‘pounded’ with local search, you might consider installing the Google search option for site search instead to not use your own dbase for content searches — some spiders/bots can whack your processes by searching your entire site links for content vs looking at sitemap only.

You can also optimize your wp-config.php file by putting in direct explicit URLs for your site, and plugins folder, content folder, so that system doesn’t need to ‘look those up’ for every path.

It all adds up!

HI, have you been able to resole this issue, because i am getting the same problem with siteground

@betawin

did you try some of my suggestions?

several things you can do – limit number of queries, don’t use the mega menu with posts, limit number of module types in use; also dumb as it sounds adding the explicit URLs in your wp-config.php file helps too (see the wordpress codex docs on how to do that).

I too am having this issue, and I plan to try everything you listed here to see if that helps, but I had another question in regards to this. Does “pre-loading” the blocks use extra resources? I forget the actual wording, but I know on some of my content blocks I have three options, something to the affect of “partial preloading” “full preload” and none. Again, preloading may not be the right wording, but I’m not in front of my computer to be able to check exactly what the options are.