paprykasz
commented
2 years ago Hey @MaxBaumR1,
thank you for your investigation and feedback!
It looks like you are right and query can be optimized at that point. We will evaluate it and if there won't be any problems it will be implemented.
Btw. You can also create pull request for this issue and any other issues that you will find in future.
If you have any further questions please don't hesitate to contact me/us at anytime.
All the best, Patryk
MaxBaumR1
Hi,
after trying to understand why the initial order import into the JTL Wawi always failed with a timeout error, i found a extreme slow sql query done by the connector:
SELECT COUNT(DISTINCT(p.ID)) FROM posts p LEFT JOIN jtl_connector_link_payment l ON l.endpoint_id = p.ID LEFT JOIN jtl_connector_link_order o ON o.endpoint_id = p.ID WHERE p.post_type = 'shop_order' AND l.host_id IS NULL AND (p.post_status = 'wc-processing' AND p.ID NOT IN (SELECT pm.post_id FROM postmeta pm WHERE pm.meta_value IN ('cod','german_market_purchase_on_account','german_market_sepa_direct_debit','cheque','bacs','cash_on_delivery','invoice')) OR p.ID IN (SELECT pm.post_id FROM postmeta pm WHERE pm.meta_value IN ('cod','german_market_purchase_on_account','german_market_sepa_direct_debit','cheque','bacs','cash_on_delivery','invoice')) AND p.post_status = 'wc-completed');
If im not mistaken it is created and done in .../wp-content/plugins/woo-jtl-connector/src/Utilities/SqlTraits/PaymentTrait.php Method: paymentCompletedPull
The problem are the subselects: SELECT pm.post_id FROM postmeta pm WHERE pm.meta_value IN ('cod','german_market_purchase_on_account','german_market_sepa_direct_debit','cheque','bacs','cash_on_delivery','invoice')
As you know there is by default no index on postmeta.meta_value. Therefore the subselect has to scan the whole table, which is in our case >4 Million rows and growing. And i see that the query is repeated a couple of times.
My understanding is you want to find all orderIds with the given payment methods. Why do you not include the key with which Woocommerce stores the payment method? Like ... pm.meta_key = '_payment_method' AND pm.meta_value IN (..
postmeta.meta_key is indexed by default and by including it in the query time would improve significantly.
Another thing is, that without limiting the query to postmeta.meta_key = '_payment_method' how do you know that the data was stored by Woocommerce and not any other plugin?
Best!