Improving performance for billing queries from accounting DB

For very large accounting databases, or complex custom queries, we have been researching performance improvements at the SQL level.

So, for the upcoming Abiquo 4.4 we are adding some indexes into that table, in the following columns:

• idVirtualMachine
• costCode
• idAccountingResourceType
• idStorageTier
• startTime

If you find a performance issue in any of your procedures using the accounting database you might want to add those indexes manually. You can do so by issuing the following statements against the kinton_accounting DB:

CREATE INDEX id_vm_detail ON kinton_accounting.accounting_event_detail (idVirtualMachine);
CREATE INDEX idx_acc_resource_type ON kinton_accounting.accounting_event_detail (idAccountingResourceType);
CREATE INDEX idx_cost_code ON kinton_accounting.accounting_event_detail (costCode);
CREATE INDEX idx_storage_tier ON kinton_accounting.accounting_event_detail (idStorageTier);
CREATE INDEX idx_start_time ON kinton_accounting.accounting_event_detail (startTime);

As usual, back up your database before performing any modification, and please raise a ticket if you have any questions or concerns about applying this in your environment.