Bug 21288 - Slowness in acquisition caused by GetInvoices
Summary: Slowness in acquisition caused by GetInvoices
Status: CLOSED FIXED
Alias: None
Product: Koha
Classification: Unclassified
Component: Acquisitions (show other bugs)
Version: Main
Hardware: All All
: P5 - low minor (vote)
Assignee: paxed
QA Contact: Testopia
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2018-08-30 08:25 UTC by paxed
Modified: 2019-10-14 19:57 UTC (History)
4 users (show)

See Also:
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 (3.47 KB, text/plain)
2018-08-30 08:32 UTC, paxed
Details
Bug 21288: Slowness in acquisition caused by GetInvoices (2.20 KB, patch)
2018-08-30 08:35 UTC, paxed
Details | Diff | Splinter Review
Bug 21288: Slowness in acquisition caused by GetInvoices (2.24 KB, patch)
2018-09-14 19:02 UTC, Mark Tompsett
Details | Diff | Splinter Review
Bug 21288: Slowness in acquisition caused by GetInvoices (2.31 KB, patch)
2018-09-15 16:41 UTC, Chris Cormack
Details | Diff | Splinter Review

Note You need to log in before you can comment on or make changes to this bug.
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