slow query

  • Resolved Henry

    (@itaticrushde)


    1 year, 5 months ago

    For a large site with thousands of commnet, the following code take 80s to run, how could this be speed up?

    SELECT n.comment_approved, COUNT( * ) AS total
FROM wp_comments AS n
LEFT JOIN wp_posts AS m
ON n.comment_post_ID = m.ID
WHERE ( n.comment_type NOT IN ( 'cr_qna' )
AND (m.post_type = 'product'
OR n.comment_post_ID IN ( 28 ) ) )
GROUP BY n.comment_approved

Viewing 8 replies - 1 through 8 (of 8 total)
  • Plugin Support pear8398

    (@pear8398)

    Hi Henry,

    Thanks for using CusRev.

    Can you please share some screenshots of the issue on your site? Do you use the plugin Query Monitor to check the loading time and queries?

    Thread Starter Henry

    (@itaticrushde)

    Yes, we tracked this using Query Monitor. This leads to a 403 error at the end.

    Plugin Support pear8398

    (@pear8398)

    Hi,

    Is this possible for you can share screenshots of the issue on your site when using Query Monitor?

    If you deactivate all plugins except CusRev, WooCommerce and switch to a standard theme of WordPress, does the slow issue persist?

    Thread Starter Henry

    (@itaticrushde)

    I got more details for the slow query. As shown in the following SQL query, too many post types are included when getting comments? Is there a way to exclude post type, e.g. shop_coupon, when query for comments?

    SELECT wp_comments.comment_ID FROM wp_comments JOIN wp_posts ON wp_posts.ID = wp_comments.comment_post_ID LEFT JOIN wp_posts AS wp_posts_to_exclude_reviews ON comment_post_ID = wp_posts_to_exclude_reviews.ID WHERE ( ( comment_approved = ‘0’ OR comment_approved = ‘1’ ) ) AND wp_posts.post_type IN (‘post’,’page’,‘attachment’,’revision’,’nav_menu_item’,’custom_css’,’customize_changeset’,’oembed_cache’,’user_request’,’wp_block’,’wp_template’,’wp_template_part’,’wp_global_styles’,’wp_navigation’,’product_variation’,’shop_order’,’shop_order_refund’,’shop_coupon’,’shop_order_placehold’) AND comment_type != ‘order_note’ AND comment_type != ‘webhook_delivery’ AND wp_posts_to_exclude_reviews.post_type NOT IN (‘product’) ORDER BY wp_comments.comment_date_gmt DESC LIMIT 0,58

    Thread Starter Henry

    (@itaticrushde)

    Here is the screen shot

    https://imgtr.ee/image/2023-10-24-07-14-37.IIUEXW

    Thread Starter Henry

    (@itaticrushde)

    This will be much faster.

    SELECT?n.comment_approved,?COUNT(?*?)?AS?total?FROM?wp_comments?AS?n?LEFT?JOIN?wp_posts?AS?m?ON?n.comment_post_ID?=?m.ID?WHERE?(?n.comment_type?NOT?IN?(?‘cr_qna’?)?AND?(m.post_type?=?‘product’?)?)?GROUP?BY?n.comment_approved;

    Plugin Support pistachio6321

    (@pistachio6321)

    Thanks for the suggestion!

    Sorry, but we’re a little bit confused – how the last snippet you posted is related to the previous one? It looks like a completely different SELECT statement, doesn’t it?

    SELECT wp_comments.comment_ID FROM wp_comments JOIN wp_posts ON wp_posts.ID = wp_comments.comment_post_ID LEFT JOIN wp_posts AS wp_posts_to_exclude_reviews ON comment_post_ID = wp_posts_to_exclude_reviews.ID WHERE ( ( comment_approved = ‘0’ OR comment_approved = ‘1’ ) ) AND wp_posts.post_type IN (‘post’,’page’,‘attachment’,’revision’,’nav_menu_item’,’custom_css’,’customize_changeset’,’oembed_cache’,’user_request’,’wp_block’,’wp_template’,’wp_template_part’,’wp_global_styles’,’wp_navigation’,’product_variation’,’shop_order’,’shop_order_refund’,’shop_coupon’,’shop_order_placehold’) AND comment_type != ‘order_note’ AND comment_type != ‘webhook_delivery’ AND wp_posts_to_exclude_reviews.post_type NOT IN (‘product’) ORDER BY wp_comments.comment_date_gmt DESC LIMIT 0,58
    Plugin Support bagel1317

    (@bagel1317)

    We haven’t heard from you for more than two weeks. For this reason, I’ll assume that either you are not interested in this question/problem anymore or it has been resolved. If you still require any help, please start a new forum topic.

Viewing 8 replies - 1 through 8 (of 8 total)
  • The topic ‘slow query’ is closed to new replies.