Zetanova
commented
1 month ago I fixed now the issue, Imagify makes a query for image count and list that will execute as an full table scan.
I fixed it by inserting two new indecies:
ALTER TABLE `wp_posts` ADD INDEX `type_status_mime` (`post_type`, `post_status`, `post_mime_type`) USING BTREE;
ALTER TABLE `wp_postmeta` ADD INDEX `post_key_value` (`post_id`, `meta_key`(191), `meta_value`(8)) USING BTREE;The issue was that the table got fragmented and mariadb picked the wrong index. After optimizing both tables, imagery option page start working again.
Here the slow_quries on a AMD Ryzen 9 7950X3D 192GB bare metal server (idling):
#ROWS 3652326 => 20sec
SELECT p.ID
FROM wp_posts AS p
INNER JOIN wp_postmeta AS imrwpmt1
ON ( p.ID = imrwpmt1.post_id AND imrwpmt1.meta_key = '_wp_attached_file' )
INNER JOIN wp_postmeta AS imrwpmt2
ON ( p.ID = imrwpmt2.post_id AND imrwpmt2.meta_key = '_wp_attachment_metadata' )
LEFT JOIN wp_postmeta AS mt1
ON ( p.ID = mt1.post_id AND mt1.meta_key = '_imagify_status' )
LEFT JOIN wp_postmeta AS mt2
ON ( p.ID = mt2.post_id AND mt2.meta_key = '_imagify_data' )
WHERE
p.post_mime_type IN ( 'image/jpeg','image/png','image/gif' )
AND (mt1.meta_key IS NULL OR mt1.meta_value = 'success' OR mt1.meta_value = 'already_optimized' )
AND mt2.meta_value NOT LIKE '%@imagify-webp\";a:4:{s:7:\"success\";b:1;%'
AND p.post_type = 'attachment'
AND p.post_status IN ( 'inherit','private','acf-disabled' )
AND imrwpmt1.meta_value NOT LIKE '%://%' AND imrwpmt1.meta_value NOT LIKE '_:\\\%' AND REVERSE (LOWER( imrwpmt1.meta_value )) REGEXP '^gpj\..*|^gepj\..*|^epj\..*|^gnp\..*|^fig\..*|^pbew\..*|^fdp\..*'
ORDER BY p.ID DESC
LIMIT 0, 10000
#ROWS 3302575 => 27sec result 367013
SELECT COUNT( p.ID )
FROM wp_posts AS p
INNER JOIN wp_postmeta AS imrwpmt1
ON ( p.ID = imrwpmt1.post_id AND imrwpmt1.meta_key = '_wp_attached_file' AND p.post_mime_type IN ( 'image/jpeg','image/png','image/gif','image/webp','application/pdf' )
AND p.post_type = 'attachment'
AND p.post_status IN ( 'inherit','private','acf-disabled' ) )
INNER JOIN wp_postmeta AS imrwpmt2
ON ( p.ID = imrwpmt2.post_id AND imrwpmt2.meta_key = '_wp_attachment_metadata' )
WHERE p.post_mime_type IN ( 'image/jpeg','image/png','image/gif','image/webp','application/pdf' )
AND p.post_type = 'attachment'
AND p.post_status IN ( 'inherit','private','acf-disabled' )
AND imrwpmt1.meta_value NOT LIKE '%://%' AND imrwpmt1.meta_value NOT LIKE '_:\\\%' AND REVERSE (LOWER( imrwpmt1.meta_value )) REGEXP '^gpj\..*|^gepj\..*|^epj\..*|^gnp\..*|^fig\..*|^pbew\..*|^fdp\..*'
saranshj4
Imagify is working on multiple web sites flawlessly, but one of them has a large media archive (image count). and this produces a timeout (60sec) issue.
The symptoms are that the "Imagify settings" and normal "media libary" page is timing out. After the deactivation of the plugin, a reactivation is not possible (timeout).
I moded the plugin 2-3 years ago and fixed the wp-query to not make full table scans, but can't remember how exactly I fixed it. What I can remember, I wrote the fix to the support over the Imagify website.
After update to the last release the issue appeared again.
If I find the fix again, I will post it here.