Tweet
Trying to clear up a few things on the forum. I've switched on the logging for mysql and long running queries. I found this:
A little digging and this appears to be from in class_dm_attachment.php. I had found mention about the attachment cleanup in the cron cleanup2.php on a web search. Our forum houses attachments in the file structure, not database. But we have a massive amount of attachments. Any pointers on how to improve this query?
# Time: 160125 21:20:38
# User@Host: XXXXXXXX[XXXXXXX] @ localhost []
# Query_time: 30.309812 Lock_time: 0.000044 Rows_sent: 315245 Rows_examined: 558351
use XXXXXXXXXX;
SET timestamp=1453778438;
SELECT a.attachmentid, fd.userid, fd.filedataid, a.userid AS auserid, a.contenttypeid
FROM filedata AS fd
LEFT JOIN attachment AS a ON (a.filedataid = fd.filedataid)
WHERE fd.refcount = 0 AND fd.dateline < 1453774807;
# User@Host: XXXXXXXX[XXXXXXX] @ localhost []
# Query_time: 30.309812 Lock_time: 0.000044 Rows_sent: 315245 Rows_examined: 558351
use XXXXXXXXXX;
SET timestamp=1453778438;
SELECT a.attachmentid, fd.userid, fd.filedataid, a.userid AS auserid, a.contenttypeid
FROM filedata AS fd
LEFT JOIN attachment AS a ON (a.filedataid = fd.filedataid)
WHERE fd.refcount = 0 AND fd.dateline < 1453774807;
Comment