Very Slow Query collapse the server

[fsalvato]

SELECT SQL_CALC_FOUND_ROWS tbl_posts.ID FROM tbl_posts LEFT JOIN tbl_postmeta ON ( tbl_posts.ID = tbl_postmeta.post_id ) LEFT JOIN tbl_postmeta AS mt1 ON (tbl_posts.ID = mt1.post_id AND mt1.meta_key = '_instant_article_options' ) WHERE 1=1 AND (
( tbl_postmeta.meta_key = '_instant_article_options' AND tbl_postmeta.meta_value NOT LIKE '%s:12:"exclude\_post";s:7:"exclude";%' )
OR
mt1.post_id IS NULL
) AND tbl_posts.post_type = 'post' AND (tbl_posts.post_status = 'publish') GROUP BY tbl_posts.ID ORDER BY tbl_posts.post_modified DESC LIMIT 0, 10

This query is very slow and is collapsing the server

Technical info

• WordPress version: 4.8
• allfacebook Instant Articles version: Last version

[nash-ye]

The exclude mechanism in this plugin is not efficient, it hits the site performance especially if you have thousands of posts and meta.

If the plugin developer would not fix this issue, we will be forced to look out for an another plugin as fast as possible. You could have a separate and boolean meta to mark the posts as excluded, this would improve the query performance.

[CyberCr33p]

I don't think it will get fixed. The plugin is not updated for many months.

Query_time: 11.671078 Lock_time: 0.000093 Rows_sent: 25 Rows_examined: 12394184
SET timestamp=1513960885;
SELECT SQL_CALC_FOUND_ROWS tmncl_posts.ID FROM tmncl_posts LEFT JOIN tmncl_postmeta ON ( tmncl_posts.ID = tmncl_postmeta.post_id ) LEFT JOIN tmncl_postmeta AS mt1 ON (tmncl_posts.ID = mt1.post_id AND mt1.meta_key = '_instant_article_options' ) WHERE 1=1 AND (
( tmncl_postmeta.meta_key = '_instant_article_options' AND tmncl_postmeta.meta_value NOT LIKE '%s:12:"exclude\_post";s:7:"exclude";%' )
OR
mt1.post_id IS NULL
) AND tmncl_posts.post_type = 'post' AND (tmncl_posts.post_status = 'publish') GROUP BY tmncl_posts.ID ORDER BY tmncl_posts.post_modified DESC LIMIT 0, 25;