Closed piotrbak closed 7 months ago
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.
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.
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' )";
}
Effort S
Well, as we discussed about it, I agree with this solution. Looks good to me :)
Before submitting an issue please check that you’ve completed the following steps:
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:
Expected behavior Queries should be optimised
Screenshots
![image](https://github.com/wp-media/imagify-plugin/assets/26767037/c42bd3c4-28fb-443b-8ebf-2aad866fe75a)
Acceptance Criteria (for WP Media team use only)