Bug 21288

Summary: Slowness in acquisition caused by GetInvoices
Product: Koha Reporter: paxed <pasi.kallinen>
Component: AcquisitionsAssignee: paxed <pasi.kallinen>
Status: CLOSED FIXED QA Contact: Testopia <testopia>
Severity: minor    
Priority: P5 - low CC: fridolin.somers, katrin.fischer, martin.renvoize, nick
Version: Main   
Hardware: All   
OS: All   
Change sponsored?: --- Patch complexity: ---
Documentation contact: Documentation submission:
Text to go in the release notes:
Version(s) released in:
Attachments: Data for the query from MySQL Explain
Bug 21288: Slowness in acquisition caused by GetInvoices
Bug 21288: Slowness in acquisition caused by GetInvoices
Bug 21288: Slowness in acquisition caused by GetInvoices

Description paxed 2018-08-30 08:25:24 UTC
The SQL query done by GetInvoices is quite slow on our production server.

# Query_time: 55.436404  Lock_time: 0.000097  Rows_sent: 231  Rows_examined: 190484

In the query, there's this join:

          LEFT JOIN subscription ON biblio.biblionumber = subscription.biblionumber

but there's no index on subscription.biblionumber. Adding the index speeds up the query so it only takes up about 3 seconds.
Comment 1 paxed 2018-08-30 08:32:59 UTC
Created attachment 78306 [details]
Data for the query from MySQL Explain
Comment 2 paxed 2018-08-30 08:35:24 UTC
Created attachment 78307 [details] [review]
Bug 21288: Slowness in acquisition caused by GetInvoices

The SQL query done by GetInvoices is quite slow on our production server,
taking about a minute.

In the query, there's this join:

  LEFT JOIN subscription ON biblio.biblionumber = subscription.biblionumber

but there's no index on subscription.biblionumber. Adding the index speeds
up the query so it only takes up about 3 seconds.
Comment 3 Katrin Fischer 2018-08-30 08:56:29 UTC
Very glad you reported and investigated this - we had quite some trouble with invoices search in one of our installations. Will try to test!
Comment 4 Mark Tompsett 2018-09-14 19:02:05 UTC
Created attachment 78708 [details] [review]
Bug 21288: Slowness in acquisition caused by GetInvoices

The SQL query done by GetInvoices is quite slow on our production server,
taking about a minute.

In the query, there's this join:

  LEFT JOIN subscription ON biblio.biblionumber = subscription.biblionumber

but there's no index on subscription.biblionumber. Adding the index speeds
up the query so it only takes up about 3 seconds.

Signed-off-by: Pasi Kallinen <pasi.kallinen@joensuu.fi>

Signed-off-by: Mark Tompsett <mtompset@hotmail.com>
Comment 5 Chris Cormack 2018-09-15 16:41:07 UTC
Created attachment 78856 [details] [review]
Bug 21288: Slowness in acquisition caused by GetInvoices

The SQL query done by GetInvoices is quite slow on our production server,
taking about a minute.

In the query, there's this join:

  LEFT JOIN subscription ON biblio.biblionumber = subscription.biblionumber

but there's no index on subscription.biblionumber. Adding the index speeds
up the query so it only takes up about 3 seconds.

Signed-off-by: Pasi Kallinen <pasi.kallinen@joensuu.fi>

Signed-off-by: Mark Tompsett <mtompset@hotmail.com>
Signed-off-by: Chris Cormack <chris@bigballofwax.co.nz>
Comment 6 Nick Clemens 2018-09-15 23:29:38 UTC
Awesome work all!

Pushed to master for 18.11
Comment 7 Martin Renvoize 2018-09-17 14:01:10 UTC
Pushed to 18.05.x for 18.05.04
Comment 8 Fridolin Somers 2018-09-24 10:06:09 UTC
Pushed to 17.11.x for 17.11.10