Support

Search results for ""

Sorry, no results found. Perhaps you would like to search the documentation?
All Topics
John Man

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!

11 months, 4 weeks ago
Stefan
Developer

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.

11 months, 4 weeks ago
John Man

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.

11 months ago
Stefan
Developer

Will do, thanks for letting us know!

11 months ago

You must be logged in to reply to this topic.