Performance issue “SELECT SQL_CALC_FOUND_ROWS”
I’m not quite sure if it is directly related to Admin Columns but I would like to see if anyone here have any insight on this.
As the database grows, the performance on query “SELECT SQL_CALC_FOUND_ROWS” is getting worse.
The query looks like below which I catched it with AWS CloudWatch:
SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts LEFT JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id AND wp_postmeta.meta_key = 'rms_rm_uniq_meta_post_id' ) LEFT JOIN wp_postmeta AS mt1 ON (wp_posts.ID = mt1.post_id AND mt1.meta_key = 'rms_remote_featured_image' ) WHERE 1=1 AND wp_posts.ID IN ( [a lots of post IDs] ) ) AND wp_posts.post_type = 'shop_order' AND ((wp_posts.post_status = 'wc-pending' OR [criteria on post_status] )) GROUP BY wp_posts.ID ORDER BY FIELD( [a lots of post IDs] ) LIMIT 0, 50
I have omitted the parameters like the list of post ID and select criteria on post_status. I’m thinking if it is related to the pagination together with admin columns but I cannot make a conclusion.
Comments and ideas are highly welcomed!
I believe this issue is caused when trying to sort on a column that can’t be optimized in one query. In that case we try to sort on a subset. Based on your query you’re trying to sort on a column on the Order overview. Can you tell me which column you’re sorting on?
We improved a lot of our sorting models to be more efficient in SQL, but some columns can’t be optimized any further.
Sorry for late reply as I was testing on different things.
The column is set in the order list of Woocommerce and it shows the SKU of the product of the order items of the order. I have removed this column for testing and the performance is better. At the end I have created an extra order meta to store the SKUs for faster querying.
I don’t think it is a problem of AC so please feel free to close this ticket.
Will do, thanks for letting us know!
You must be logged in to reply to this topic.