slow query

Resolved

(@itaticrushde)

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)

2 years, 8 months ago

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)

2 years, 7 months ago

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

Plugin Support pear8398
(@pear8398)

2 years, 7 months ago

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)

2 years, 7 months ago

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)

2 years, 7 months ago

Here is the screen shot

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

Thread Starter Henry
(@itaticrushde)

2 years, 7 months ago

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)

2 years, 5 months ago
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)

2 years, 5 months ago

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.