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

        tyson Tyson Phillips (Inactive) created issue -
        tyson Tyson Phillips (Inactive) made changes -
        Field Original Value New Value
        Rank Ranked higher
        tyson Tyson Phillips (Inactive) made changes -
        Story Points 5
        tyson Tyson Phillips (Inactive) made changes -
        Sprint 4.9.0 Sprint 3 [ 101 ]
        tyson Tyson Phillips (Inactive) made changes -
        Rank Ranked higher
        tyson Tyson Phillips (Inactive) made changes -
        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.

        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.
        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.
        tyson Tyson Phillips (Inactive) made changes -
        Sprint 4.9.0 Sprint 3 [ 101 ] 4.9.0 Sprint 3, 4.9.0 Sprint 4 [ 101, 102 ]
        tyson Tyson Phillips (Inactive) made changes -
        Rank Ranked higher
        jonathan Jonathan Reissmueller made changes -
        Assignee Jonathan Reissmueller [ jonathan ]
        Automated transition triggered when Jonathan Reissmueller created a branch in Stash -
        Status Open [ 1 ] In Progress [ 3 ]
        jonathan Jonathan Reissmueller made changes -
        Remaining Estimate 0 minutes [ 0 ]
        Time Spent 5 hours, 27 minutes [ 19620 ]
        Worklog Id 13212 [ 13212 ]
        Automated transition triggered when Jonathan Reissmueller created pull request #797 in Stash -
        Status In Progress [ 3 ] In Review [ 5 ]
        Resolution Fixed [ 1 ]
        tyson Tyson Phillips (Inactive) made changes -
        Sprint 4.9.0 Sprint 3, 4.9.0 Sprint 4 [ 101, 102 ] 4.9.0 Sprint 3, 4.9.0 Sprint 4, 4.9.0 Sprint 5 [ 101, 102, 103 ]
        tyson Tyson Phillips (Inactive) made changes -
        Rank Ranked higher
        jonathan Jonathan Reissmueller made changes -
        Time Spent 5 hours, 27 minutes [ 19620 ] 6 hours, 5 minutes [ 21900 ]
        Worklog Id 13233 [ 13233 ]
        Automated transition triggered when Tyson Phillips (Inactive) merged pull request #797 in Stash -
        Status In Review [ 5 ] Closed [ 6 ]

          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