21 Mar 2026

MySQL/MariaDB: Improve Invoiceplane Speed with Indexes

InvoicePlane is a self-hosted open source application for managing your quotes, invoices, clients and payments. https://github.com/InvoicePlane/InvoicePlane If you have something like 500 clients in the system and about 6500 invoices, you may notice a remarkable slowdown when you display clients or invoices overview. The solution: Create indexes in MySQL / Mariad.

• create the indexes, try again, enjoy!
• before: 7…8 seconds on viewing clients list, after: 0.5 seconds

-- clients active
CREATE INDEX idx_clients_active 
  ON ip_clients (client_active);

-- Invoices Clients idx
CREATE INDEX idx_invoices_client_id
  ON ip_invoices (client_id);

-- Join client invoice
CREATE INDEX idx_invoices_client_id
  ON ip_invoices (client_id);

-- Amount
CREATE INDEX idx_invoice_amounts_invoice_id
  ON ip_invoice_amounts (invoice_id);

-- Clients (idx)
CREATE INDEX idx_clients_id ON ip_clients (client_id);

-- Invoices (important!)
CREATE INDEX idx_invoices_user_id
  ON ip_invoices (user_id);

-- Recurring (Subquery!)
CREATE INDEX idx_invoices_recurring_invoice_id
  ON ip_invoices_recurring (invoice_id, recur_next_date);

-- Quotes
CREATE INDEX idx_quotes_invoice_id
  ON ip_quotes (invoice_id);

-- Sumex
CREATE INDEX idx_invoice_sumex_invoice
  ON ip_invoice_sumex (sumex_invoice);

• How did i find that out?
• insert this in the controller in the correspodending method like status:

$this->output->enable_profiler(TRUE);

• make one click: Clients -> View Cliens
• now look on the SQL output at the bottom of the page
• EXPLAIN this output with EXPLAIN SELECT … in mysql / mariabd
• Think, generate indexes
• it is also possible to optimize the sub-query which will bring another major speed upgrade, but thats for another post