I am trying to:
It seems like the MySQL database will crash when users select many filter selected like 12+.
Link to a page where the issue can be seen:
hidden link
The view plugin seems like using INNER JOIN for each filter.
Here is the example query:
WordPress database error Query execution was interrupted, query_timeout exceeded for query
```
SELECT wp_posts.ID
FROM wp_posts INNER JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id ) INNER JOIN wp_postmeta AS mt1 ON ( wp_posts.ID = mt1.post_id ) INNER JOIN wp_postmeta AS mt2 ON ( wp_posts.ID = mt2.post_id ) INNER JOIN wp_postmeta AS mt3 ON ( wp_posts.ID = mt3.post_id ) INNER JOIN wp_postmeta AS mt4 ON ( wp_posts.ID = mt4.post_id ) INNER JOIN wp_postmeta AS mt5 ON ( wp_posts.ID = mt5.post_id ) INNER JOIN wp_postmeta AS mt6 ON ( wp_posts.ID = mt6.post_id ) INNER JOIN wp_postmeta AS mt7 ON ( wp_posts.ID = mt7.post_id ) INNER JOIN wp_postmeta AS mt8 ON ( wp_posts.ID = mt8.post_id ) INNER JOIN wp_postmeta AS mt9 ON ( wp_posts.ID = mt9.post_id ) INNER JOIN wp_postmeta AS mt10 ON ( wp_posts.ID = mt10.post_id ) INNER JOIN wp_postmeta AS mt11 ON ( wp_posts.ID = mt11.post_id ) INNER JOIN wp_postmeta AS mt12 ON ( wp_posts.ID = mt12.post_id )
WHERE 1=1 AND (
( wp_postmeta.meta_key = 'wpcf-portfolio' AND wp_postmeta.meta_value = 'Nutrition' )
AND
(
( mt1.meta_key = 'wpcf-nutrition-filter' AND mt1.meta_value LIKE '%wpcf-fields-checkboxes-option-bf0723721301c42a9326a0437d99f81c-1%' )
AND
( mt2.meta_key = 'wpcf-nutrition-filter' AND mt2.meta_value LIKE '%wpcf-fields-checkboxes-option-909769b52c4a4f90d2cb281983ed5456-1%' )
AND
( mt3.meta_key = 'wpcf-nutrition-filter' AND mt3.meta_value LIKE '%wpcf-fields-checkboxes-option-b996b0345c152bf51a269b4e5d3d0566-1%' )
AND
( mt4.meta_key = 'wpcf-nutrition-filter' AND mt4.meta_value LIKE '%wpcf-fields-checkboxes-option-a1f3fc258b0ca0a1e5a5baa2dab27b36-1%' )
AND
( mt5.meta_key = 'wpcf-nutrition-filter' AND mt5.meta_value LIKE '%wpcf-fields-checkboxes-option-48f0206a1c55e36e39e1744cf805f46f-1%' )
AND
( mt6.meta_key = 'wpcf-nutrition-filter' AND mt6.meta_value LIKE '%wpcf-fields-checkboxes-option-6acfc18eaf9c9950fcf437cd432e99c3-1%' )
AND
( mt7.meta_key = 'wpcf-nutrition-filter' AND mt7.meta_value LIKE '%wpcf-fields-checkboxes-option-8beace4f0e664b122c8ca17402c614f2-1%' )
AND
( mt8.meta_key = 'wpcf-nutrition-filter' AND mt8.meta_value LIKE '%wpcf-fields-checkboxes-option-294bdbd0d3137b18621efff33d873ed4-1%' )
AND
( mt9.meta_key = 'wpcf-nutrition-filter' AND mt9.meta_value LIKE '%wpcf-fields-checkboxes-option-6a00ac2bc12538114d08b2a0ff276ad5-1%' )
AND
( mt10.meta_key = 'wpcf-nutrition-filter' AND mt10.meta_value LIKE '%wpcf-fields-checkboxes-option-e7ac3d228ca02299e4b817cb2274e0b3-1%' )
AND
( mt11.meta_key = 'wpcf-nutrition-filter' AND mt11.meta_value LIKE '%wpcf-fields-checkboxes-option-05e2d0055b795cb6022f2bf857c6af9a-1%' )
)
AND
( mt12.meta_key = 'wpcf-needs' AND mt12.meta_value LIKE '%wpcf-fields-checkboxes-option-97b2871bca75eb745e3f7149e9ac51da-1%' )
) AND wp_posts.post_type = 'product' AND ((wp_posts.post_status = 'publish'))
GROUP BY wp_posts.ID
ORDER BY wp_posts.post_title ASC
Inner join is very slow.
Is it necessary to use INNER JOIN for selecting filters?
Hi there,
From what I see it seems necessary to use INNER JOIN, what else do you suggest to be used instead?
Just to be sure that we are on the same page, if there is a suggestion I can ask the dev team, but the implementation of such suggestion will be considered as a feature request and subject to that procedure.
Thank you.
