Very slow query with large media library

[piotrbak]

Before submitting an issue please check that you’ve completed the following steps:

• Made sure you’re on the latest version
• Used the search feature to ensure that the bug hasn’t been reported before

Describe the bug
Some queries are extremely slow when being used on the website with bug media library.

To Reproduce
Steps to reproduce the behavior:

1. Install Imagify Plugin on a website with big media library (https://rocketlabsqa.ovh)
2. Enable Query Monitor plugin
3. Go to Imagify Settings
4. See error and extremely slow loading time

Expected behavior
Queries should be optimised

Screenshots

Acceptance Criteria (for WP Media team use only)

1. No changes in displayed number of optimized images in Bulk Optimization, Imagify Settings, Media sections
2. No changes in displayed number of all images in Bulk Optimization, Imagify Settings, Media sections

[CrochetFeve0251]

Both of that queries could be optimized by filtering on the post table then join.
For that we could do a request close to that format:

 SELECT p.ID FROM wp_posts AS p LEFT 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' ) LEFT JOIN wp_postmeta AS imrwpmt2 ON ( p.ID = imrwpmt2.post_id AND imrwpmt2.meta_key = '_wp_attachment_metadata' ) WHERE    imrwpmt2.meta_value IS NULL OR imrwpmt1.meta_value IS NULL OR imrwpmt1.meta_value LIKE '%://%' OR imrwpmt1.meta_value LIKE '_:\\%' OR ( LOWER( imrwpmt1.meta_value ) NOT LIKE '%.jpg' AND LOWER( imrwpmt1.meta_value ) NOT LIKE '%.jpeg' AND LOWER( imrwpmt1.meta_value ) NOT LIKE '%.jpe' AND LOWER( imrwpmt1.meta_value ) NOT LIKE '%.png' AND LOWER( imrwpmt1.meta_value ) NOT LIKE '%.gif' AND LOWER( imrwpmt1.meta_value ) NOT LIKE '%.webp' AND LOWER( imrwpmt1.meta_value ) NOT LIKE '%.pdf' ) LIMIT 1

To achieve that we could remove the conditions on post here.
Create a new parameter on the method get_required_wp_metadata_join_clause and pass the conditions to there.
Inside get_required_wp_metadata_join_clause could then use theses condtions here to filter before the join.

[CrochetFeve0251]

Root cause

The root cause is that we have first making the join with the full tables with will try to match all rows from both tables one by one.

Scope a solution

The solution for that is to filter the table before the join.

To achieve that we could remove the conditions on post here.
Create a new parameter on the method get_required_wp_metadata_join_clause which is an empty string by default and pass the conditions to there.
Inside get_required_wp_metadata_join_clause could then use theses condtions here to filter before the join:

		$join = $matching ? 'INNER' : 'LEFT';

               $first = true;

		foreach ( self::get_required_wp_metadata_aliases() as $meta_name => $alias ) {

                       if($first ) {
                        $clause .= "
			$join JOIN $wpdb->postmeta AS $alias
				ON ( $id_field = $alias.post_id AND $alias.meta_key = '$meta_name' $conditions)";
                        continue;
                      }

			$clause .= "
			$join JOIN $wpdb->postmeta AS $alias
				ON ( $id_field = $alias.post_id AND $alias.meta_key = '$meta_name' )";
		}

Estimate effort

Effort S

[Miraeld]

Well, as we discussed about it, I agree with this solution. Looks good to me :)