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

            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.

              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