I wonder why it's not caching. I followed your instructions. Even with the plugin, would it solve the problem?
My web host sent me this message. Can we resolve this or should I find another host?
======
The things that determine how databases are utilized are how the queries
are written. A single badly written query could hypothetically examine
infinite numbers of rows in a fairly small table. A handful of poorly
optimized queries could clog up the works and cause issues for an entire
shared database server (which is worth mentioning is separate hardware that
only exists to run MySQL).
Unfortunately I'm not a MySQL admin so the best I can offer with this one
database that's been locked is a copy of slow_log entries and hope the
queries mean something to you.
# Time: 2022-08-16T07:37:10.660550Z
# User@Host: MyName_3[MyName_3] @ [xxx.xx.x.xxx] Id: 2779088
# Query_time: 15.251633 Lock_time: 0.000188 Rows_sent: 1132
Rows_examined: 73482
use MyName_copp;
SET timestamp=1660635430;
SELECT c.cid, r.aid, COUNT(pid) AS pic_count, MAX(pid) AS last_pid,
MAX(ctime) AS last_upload, depth AS level, lft
FROM cpg130_categories AS c
INNER JOIN cpg130_albums AS r ON r.category = c.cid
INNER JOIN cpg130_pictures AS p ON p.aid = r.aid
WHERE c.depth >= 0 + 1
AND approved = 'YES'
--
# Time: 2022-08-16T11:54:25.899764Z
# User@Host: MyName_3[MyName_3] @ [xxx.xx.x.xxx] Id: 2837084
# Query_time: 21.519374 Lock_time: 0.000195 Rows_sent: 1132
Rows_examined: 73482
use MyName_copp;
SET timestamp=1660650865;
SELECT c.cid, r.aid, COUNT(pid) AS pic_count, MAX(pid) AS last_pid,
MAX(ctime) AS last_upload, depth AS level, lft
FROM cpg130_categories AS c
INNER JOIN cpg130_albums AS r ON r.category = c.cid
INNER JOIN cpg130_pictures AS p ON p.aid = r.aid
WHERE c.depth >= 0 + 1
AND approved = 'YES'
--
# Time: 2022-08-16T13:06:34.260492Z
# User@Host: MyName_3[MyName_3] @ [xxx.xx.x.xxx] Id: 2853877
# Query_time: 10.179615 Lock_time: 0.000284 Rows_sent: 1 Rows_examined:
70053
use MyName_copp;
SET timestamp=1660655194;
SELECT COUNT(*) FROM cpg130_pictures WHERE ((aid='399' AND aid NOT IN
(778, 814, 934, 935, 936, 937, 938, 939) ) OR (keywords like '%japanlake%'
AND aid NOT IN (778, 814, 934, 935, 936, 937, 938, 939) )) AND
approved='YES';
--
# Time: 2022-08-16T15:00:36.193039Z
# User@Host: MyName_3[MyName_3] @ [xxx.xx.x.xxx] Id: 2876820
# Query_time: 14.899287 Lock_time: 0.000193 Rows_sent: 1132
Rows_examined: 73482
use MyName_copp;
SET timestamp=1660662036;
SELECT c.cid, r.aid, COUNT(pid) AS pic_count, MAX(pid) AS last_pid,
MAX(ctime) AS last_upload, depth AS level, lft
FROM cpg130_categories AS c
INNER JOIN cpg130_albums AS r ON r.category = c.cid
INNER JOIN cpg130_pictures AS p ON p.aid = r.aid
WHERE c.depth >= 0 + 1
AND approved = 'YES'
I know nothing about this particular site or how it's designed and as such
I can't tell why these queries are mostly sending around 1000 rows and
examining around 73k rows in response.
Granted these are only going to be queries that exceeded 10 seconds
run-time as well so it's entirely possible you're getting frequent queries
running 9.9 seconds and ending without being logged.
=========