Uploaded image for project: 'Blesta Core'
  1. Blesta Core
  2. CORE-3479

Improve performance of unioned selection queries

    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.

        Activity

          People

          • Assignee:
            jonathan Jonathan Reissmueller
            Reporter:
            tyson Tyson Phillips (Inactive)
          • Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:
              Fix Release Date:
              31/Mar/20

              Time Tracking

              Estimated:
              Original Estimate - Not Specified
              Not Specified
              Remaining:
              Remaining Estimate - 0 minutes
              0m
              Logged:
              Time Spent - 6 hours, 5 minutes
              6h 5m

                Agile