Bug 33488

Summary: Library transfer limits should have an index on fromBranch
Product: Koha Reporter: Nick Clemens (kidclamp) <nick>
Component: Architecture, internals, and plumbingAssignee: Nick Clemens (kidclamp) <nick>
Status: CLOSED FIXED QA Contact: Katrin Fischer <katrin.fischer>
Severity: normal    
Priority: P5 - low CC: aspencatteam
Version: Main   
Hardware: All   
OS: All   
See Also: https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=33489
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
Circulation function:
Attachments: Bug 33488: Add fromBranch index to branch_transfer_limits
Bug 33488: Add fromBranch index to branch_transfer_limits
Bug 33488: Add fromBranch index to branch_transfer_limits
Bug 33488: Add fromBranch index to branch_transfer_limits
Bug 33488: (QA follow-up) Fix index name

Description Nick Clemens (kidclamp) 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 (kidclamp) 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 (kidclamp) 2023-04-19 19:07:49 UTC Comment hidden (obsolete)
Comment 4 Nick Clemens (kidclamp) 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 (tcohen) 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 (tcohen) 2023-05-12 20:54:36 UTC
Pushed to master for 23.05.

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

Pushed to 22.11.x for the next release