Slow DB query in YotpoSync cron job

[enl]

The query to retrieve list of orders to sync becomes bloody slow in certain cases:

https://github.com/YotpoLtd/magento2-module-yotpo-reviews/blob/master/Model/Jobs/OrdersSync.php#L179

This query looks like this in pure SQL:

SELECT 
    `main_table`.*, 
    `yotpo_sync`.`sync_flag` AS `yotpo_sync_flag` 
FROM `sales_order` AS `main_table`
LEFT JOIN `yotpo_sync` ON main_table.entity_id = yotpo_sync.entity_id AND main_table.store_id = yotpo_sync.store_id AND yotpo_sync.entity_type = 'orders' 
WHERE 
    (`main_table`.`status` IN('complete')) 
    AND (`main_table`.`store_id` = '1') 
    AND (`main_table`.`created_at` >= '2019-09-15 09:50:45') 
    AND (`yotpo_sync`.`sync_flag` = 0 OR `yotpo_sync`.`sync_flag` IS NULL) 
ORDER BY main_table.created_at ASC

In our case, there are 300k rows in sales_order table that suit this query. And using left join on a subset of this size (especially checking on emptiness) is slow thing.

This query is being executed for about 3 seconds on our database. Not a big deal? Multiply it by ~60 store views and the fact that cronjob is run every minute by default :)

Improvement Idea

1. Add an index on sync_flag column
2. Add a row to yotpo_sync table once order is created (only adding index reduces query time only to ~1.5s which is still huge amount of time)
3. Switch to inner join with only sync_flag = 0 condition.

[pinchas13]

Hey @enl , Thanks for the report! We are currently checking the issue you have raised regarding the query slowness. I will get back to you with an update about it.

[pinchas13]

Hey @enl We are going with a new design to export orders. This issue will be addressed in future releases of the Yotpo plugin

[sensi-uros]

Hi @pinchas13, this issue wasn't addressed yet I assume? We would really need this sync to work, but have 7.000.000+ entries in sales_order table... Which makes it totally unusable. Cron took 1.1 hours last time syncing only 50 orders per store (we have over 60 active at the moment.). Any upgrades in short-term plan? Thanks!