Details
-
Type:
Improvement
-
Status: Closed
-
Priority:
Major
-
Resolution: Fixed
-
Affects Version/s: None
-
Fix Version/s: 4.9.0-b1
-
Component/s: None
-
Labels:None
Description
The Payment Reminders cron task (and possibly the Autodebit one) can be slow for some users when it fetches invoices for autodebit. This is because a union takes place for client settings via Invoices::getClientSettingSubquery. An EXPLAIN shows that the query uses filesort to order the result set, but the result set shouldn't need to be ordered. That can be alleviated by updating each union query to specifically ORDER BY NULL. However, MySQL will ignore ORDER BY NULL unless there is a LIMIT clause, so a LIMIT clause will need to be added with a sufficiently large number to return all results. This should be set to the maximum integer size if available, e.g. LIMIT 9999999999.
The end result should also have an order/limit clause to avoid filesorts in the query plan.
There are other places we perform unions for queries, such as Clients and Client Groups models. These may also benefit from the order by/limit clause additions. Testing will need to be done with a large data set for a more realistic comparison.
