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

Improve performance of invoice creation

        Details

        • Type: Improvement
        • Status: Closed
        • Priority: Major
        • Resolution: Fixed
        • Affects Version/s: None
        • Fix Version/s: 3.4.3, 3.5.0-b1
        • Component/s: None
        • Labels:
          None

          Description

          When invoices are generated there is a sub-optimal query being run.

          SELECT SUM(`transaction_applied`.`amount`) AS `total` FROM 
`transaction_applied` INNER JOIN `invoices` ON `invoices`.`id` = 
`transaction_applied`.`invoice_id` WHERE `invoices`.`status` IN 
('active','proforma') AND `invoices`.`currency` = 'USD' AND 
`invoices`.`client_id` = '12693' AND `invoices`.`date_closed` IS NULL 
AND `invoices`.`date_billed` <= '2015-03-19 15:55:19' GROUP BY 
`invoices`.`client_id`

            Activity

            Ascending order - Click to sort in descending order
            cody Cody Phillips (Inactive) created issue -
            cody Cody Phillips (Inactive) made changes -
            Field Original Value New Value
            Rank Ranked higher
            cody Cody Phillips (Inactive) made changes -
            Rank Ranked higher
            Hide
            Permalink
            cody Cody Phillips (Inactive) added a comment - - edited

            Solution is to add an index for invoice ID on transaction_applied:

            ALTER TABLE `transaction_applied` ADD INDEX `invoice_id` ( `invoice_id` )

            OR

            -ALTER TABLE `transaction_applied` ADD INDEX `invoice_id` ( `invoice_id` , `date` )-

            Above not needed since the number of transactions applied to each invoice is very small.

            Show
            cody Cody Phillips (Inactive) added a comment - - edited Solution is to add an index for invoice ID on transaction_applied: ALTER TABLE `transaction_applied` ADD INDEX `invoice_id` ( `invoice_id` ) OR - ALTER TABLE `transaction_applied` ADD INDEX `invoice_id` ( `invoice_id` , `date` ) - Above not needed since the number of transactions applied to each invoice is very small.
            cody Cody Phillips (Inactive) made changes -
            Security Private [ 10000 ]
            admin Paul Phillips made changes -
            Fix Version/s 3.4.3 [ 10613 ]
            cody Cody Phillips (Inactive) made changes -
            Status Open [ 1 ] In Progress [ 3 ]
            cody Cody Phillips (Inactive) made changes -
            Fix Version/s 3.5.0 [ 10401 ]
            cody Cody Phillips (Inactive) made changes -
            Status In Progress [ 3 ] Resolved [ 5 ]
            Resolution Fixed [ 1 ]
            tyson Tyson Phillips (Inactive) made changes -
            Status Resolved [ 5 ] Closed [ 6 ]

              People

              • Assignee:
                cody Cody Phillips (Inactive)
                Reporter:
                cody Cody Phillips (Inactive)
              • Votes:
                0 Vote for this issue
                Watchers:
                1 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved:
                  Fix Release Date:
                  25/Mar/15