Bug 33488 - Library transfer limits should have an index on fromBranch
Summary: Library transfer limits should have an index on fromBranch
Status: RESOLVED FIXED
Alias: None
Product: Koha
Classification: Unclassified
Component: Architecture, internals, and plumbing (show other bugs)
Version: master
Hardware: All All
: P5 - low normal (vote)
Assignee: Nick Clemens
QA Contact: Katrin Fischer
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2023-04-11 16:08 UTC by Nick Clemens
Modified: 2023-12-28 20:44 UTC (History)
1 user (show)

See Also:
Change sponsored?: ---
Patch complexity: Medium patch
Documentation contact:
Documentation submission:
Text to go in the release notes:
Version(s) released in:
23.05.00,22.11.06


Attachments
Bug 33488: Add fromBranch index to branch_transfer_limits (2.01 KB, patch)
2023-04-11 17:48 UTC, Nick Clemens
Details | Diff | Splinter Review
Bug 33488: Add fromBranch index to branch_transfer_limits (2.06 KB, patch)
2023-04-19 19:07 UTC, Nick Clemens
Details | Diff | Splinter Review
Bug 33488: Add fromBranch index to branch_transfer_limits (2.06 KB, patch)
2023-04-19 19:27 UTC, Nick Clemens
Details | Diff | Splinter Review
Bug 33488: Add fromBranch index to branch_transfer_limits (2.11 KB, patch)
2023-04-22 13:21 UTC, Katrin Fischer
Details | Diff | Splinter Review
Bug 33488: (QA follow-up) Fix index name (975 bytes, patch)
2023-05-12 20:46 UTC, Tomás Cohen Arazi
Details | Diff | Splinter Review

Note You need to log in before you can comment on or make changes to this bug.
Description Nick Clemens 2023-04-11 16:08:00 UTC
In a consortium with many libraries, the branch transfer limits can become complex and have many rows - we have seen 750k+ 

At this size, searching on limits can take 1/2 a second or more - and when a record has many items this begins to add up fast

Adding an index to the fromBranch column greatly reduces the cost of a lookup:
CREATE INDEX fromBranch_index ON branch_transfer_limits (fromBranch);
Comment 1 Nick Clemens 2023-04-11 17:48:18 UTC
Created attachment 149458 [details] [review]
Bug 33488: Add fromBranch index to branch_transfer_limits

To test:
1 - Enable UseBranchTransferLimits by item type
2 - Set some limits for book
3 - Place a hold, verify that pikcup dropdown reflects the limits before and after patch
Comment 2 AspenCat Team 2023-04-19 15:24:20 UTC
Tested this on our test server and compared it to our production server, which has more resources and the test server worked far faster.  This will be very helpful for our libraries - Bob
Comment 3 Nick Clemens 2023-04-19 19:07:49 UTC Comment hidden (obsolete)
Comment 4 Nick Clemens 2023-04-19 19:27:56 UTC
Created attachment 149913 [details] [review]
Bug 33488: Add fromBranch index to branch_transfer_limits

To test:
1 - Enable UseBranchTransferLimits by item type
2 - Set some limits for book
3 - Place a hold, verify that pikcup dropdown reflects the limits before and after patch

Signed-off-by: Bob Bennhoff <bbennhoff@clicweb.org>
Comment 5 Katrin Fischer 2023-04-22 13:21:31 UTC
Created attachment 150077 [details] [review]
Bug 33488: Add fromBranch index to branch_transfer_limits

To test:
1 - Enable UseBranchTransferLimits by item type
2 - Set some limits for book
3 - Place a hold, verify that pikcup dropdown reflects the limits before and after patch

Signed-off-by: Bob Bennhoff <bbennhoff@clicweb.org>
Signed-off-by: Katrin Fischer <katrin.fischer.83@web.de>
Comment 6 Tomás Cohen Arazi 2023-05-12 20:46:06 UTC
Created attachment 151161 [details] [review]
Bug 33488: (QA follow-up) Fix index name

Signed-off-by: Tomas Cohen Arazi <tomascohen@theke.io>
Comment 7 Tomás Cohen Arazi 2023-05-12 20:54:36 UTC
Pushed to master for 23.05.

Nice work everyone, thanks!
Comment 8 Martin Renvoize 2023-05-16 06:52:47 UTC
Many hands makes light work, thankyou everyone!

Pushed to 22.11.x for the next release