Optimize SQL Queries for image counting and statistics

[jeawhanlee]

Is your feature request related to a problem? Please describe.
Slow queries can some times cause time out on affected pages. see here

Describe the solution you'd like
Optimize the SQL Queries for the image counting and statistics

• imagify-plugin/inc/functions/admin-stats.php

  Line 12 in e046686

  function imagify_count_attachments() {

• imagify-plugin/inc/functions/admin-stats.php

  Line 70 in e046686

  function imagify_count_error_attachments() {

• imagify-plugin/inc/functions/admin-stats.php

  Line 121 in e046686

  function imagify_count_optimized_attachments() {

• imagify-plugin/inc/functions/admin-stats.php

  Line 233 in e046686

  function imagify_count_saving_data( $key = '' ) {

Describe alternatives you've considered
A clear and concise description of any alternative solutions or features you've considered.

Additional context
Add any other context or screenshots about the feature request here.

[Khadreal]

Scope a solution ✅

For imagify_count_attachments, imagify_count_error_attachments , imagify_count_optimized_attachments which queries are a bit similar we should use EXISTS instead of Inner join as we currently have, this means creating a new function in here

For imagify_count_saving_data we should modify this query

imagify-plugin/inc/functions/admin-stats.php

Lines 312 to 322 in e046686

	SELECT p.ID
	FROM $wpdb->posts AS p
	$nodata_join
	INNER JOIN $wpdb->postmeta AS mt1
	ON ( p.ID = mt1.post_id AND mt1.meta_key = '_imagify_status' )
	WHERE p.post_mime_type IN ( $mime_types )
	AND p.post_type = 'attachment'
	AND p.post_status IN ( $statuses )
	AND mt1.meta_value = 'success'
	$nodata_where
	ORDER BY CAST( p.ID AS UNSIGNED )"

to something like this
Changed the Order by Cast and inner join to exist.

$wpdb->prepare(
        "SELECT p.ID
        FROM {$wpdb->posts} AS p
            %1s 
        WHERE p.post_mime_type IN (%s)
            AND p.post_type = %s
            AND p.post_status IN (%s)
            AND EXISTS (
                SELECT 1 
                FROM {$wpdb->postmeta} AS mt1
                WHERE mt1.post_id = p.ID 
                    AND mt1.meta_key = %s
                    AND mt1.meta_value = %s
            )
            %2s 
        ORDER BY p.ID + 0",
        [
            $nodata_join,
            implode(',', array_fill(0, count($mime_types), '%s')),
            'attachment',
            implode(',', array_fill(0, count($statuses), '%s')),
            '_imagify_status',
            'success',
            $nodata_where
        ]
    )

[Update]
Did a quick test with the new suggested query with the available data, the new queries response are faster
imagify_count_saving_data new query was about 12ms compared to the current 19ms
For others the different wasn't much, this could be due to the amount of images I have on my test site.

[wordpressfan]

Thanks @Khadreal,

May I ask, Do u think this change will enhance the query time? did u do any tests to make sure that this will help? if yes, plz share them so we all can discuss about the best approach here.

[Khadreal]

@wordpressfan I have updated the grooming with that

[jeawhanlee]

LGTM for a quick fix for now.