Degraded SQL query

[Zetanova]

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.

[Zetanova]

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]

@Zetanova

Hello,

The following problem has already been reported: #632 and developers are aware of it. The report mentions a workaround that you can try.

Best Regards,

[Zetanova]

@saranshj4 I already made a workaround by adding new indexed to the wp table as mention above.
My report shows the root case the 2x "full-table-scan"