[Bug 39920] New: do_check_for_previous_checkout should us 'IN' over 'OR'
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39920 Bug ID: 39920 Summary: do_check_for_previous_checkout should us 'IN' over 'OR' Change sponsored?: --- Product: Koha Version: Main Hardware: All OS: All Status: NEW Severity: normal Priority: P5 - low Component: Architecture, internals, and plumbing Assignee: koha-bugs@lists.koha-community.org Reporter: nick@bywatersolutions.com QA Contact: testopia@bugs.koha-community.org When forming the query for previous checkout we have: 876 # Create (old)issues search criteria 877 my $criteria = { 878 borrowernumber => $self->borrowernumber, 879 itemnumber => \@item_nos, 880 }; This forms a query like: SELECT COUNT( * ) FROM `issues` `me` WHERE ( ( `borrowernumber` = ? AND ( `itemnumber` = ? OR `itemnumber` = ? OR `itemnumber` = ? OR `itemnumber` = ? OR `itemnumber` = ? OR `itemnumber` = ? OR `itemnumber` = ? OR `itemnumber` = ? OR `itemnumber` = ? OR `itemnumber` = ? OR `itemnumber` = ? OR `itemnumber` = ? OR `itemnumber` = ? OR `itemnumber` = ? OR `itemnumber` = ? OR `itemnumber` = ? OR `itemnumber` = ? OR `itemnumber` = ? OR `itemnumber` = ? OR `itemnumber` = ? OR `itemnumber` = ? OR `itemnumber` = ? OR `itemnumber` = ? OR `itemnumber` = ? OR `itemnumber` = ? OR `itemnumber` = ? OR `itemnumber` = ? OR `itemnumber` = ? OR `itemnumber` = ? OR `itemnumber` = ? OR `itemnumber` = ? OR `itemnumber` = ? OR `itemnumber` = ? OR `itemnumber` = ? OR `itemnumber` = ? OR `itemnumber` = ? OR `itemnumber` = ? OR `itemnumber` = ? OR `itemnumber` = ? OR `itemnumber` = ? OR `itemnumber` = ? OR `itemnumber` = ? OR `itemnumber` = ? OR `itemnumber` = ? OR `itemnumber` = ? OR `itemnumber` = ? OR `itemnumber` = ? OR `itemnumber` = ? OR `itemnumber` = ? OR `itemnumber` = ? OR `itemnumber` = ? OR `itemnumber` = ? ) ) ): If we instead do: itemnumber => { -in => \@item_nos } We get a query like: SELECT COUNT( * ) FROM `issues` `me` WHERE ( ( `borrowernumber` = ? AND `itemnumber` IN ( ?, ?, ?, ? ) ) ) I believe in the past we fond that MySQL has optimizations for IN and it shortens the query in logs -- You are receiving this mail because: You are watching all bug changes. You are the assignee for the bug.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39920 Nick Clemens (kidclamp) <nick@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|NEW |Needs Signoff -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39920 --- Comment #1 from Nick Clemens (kidclamp) <nick@bywatersolutions.com> --- Created attachment 182528 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=182528&action=edit Bug 39920: Update query for check previous checkout to use IN This patch simply alters the query fro checkign previous checkouts to use IN instead of a list of OR for itemnumbers To test: 1 - On the command line: export DBICE_TRACE=1 2 - perl -e 'use Koha::Patrons;my $patron = Koha::Patrons->find(5);my $item = Koha::Items->find(54);warn $item->itemnumber;$patron->do_check_for_previous_checkout( $item->unblessed );' 3 - Note the query 4 - Apply patch 5 - Repeat 2 6 - Note the shorter query -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39920 Nick Clemens (kidclamp) <nick@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Assignee|koha-bugs@lists.koha-commun |nick@bywatersolutions.com |ity.org | See Also| |https://bugs.koha-community | |.org/bugzilla3/show_bug.cgi | |?id=33691 -- You are receiving this mail because: You are watching all bug changes. You are the assignee for the bug.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39920 David Nind <david@davidnind.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Needs Signoff |Signed Off -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39920 David Nind <david@davidnind.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #182528|0 |1 is obsolete| | --- Comment #2 from David Nind <david@davidnind.com> --- Created attachment 182563 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=182563&action=edit Bug 39920: Update query for check previous checkout to use IN This patch alters the query for checking previous checkouts to use IN instead of a list of OR for itemnumbers. To test: 1 - On the command line: export DBIC_TRACE=1 2 - perl -e 'use Koha::Patrons;my $patron = Koha::Patrons->find(5);my $item = Koha::Items->find(54);warn $item->itemnumber;$patron->do_check_for_previous_checkout( $item->unblessed );' 3 - Note the query 4 - Apply patch 5 - Repeat 2 6 - Note the shorter query Signed-off-by: David Nind <david@davidnind.com> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39920 David Nind <david@davidnind.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Keywords| |no-sandbox CC| |david@davidnind.com --- Comment #3 from David Nind <david@davidnind.com> --- Testing notes (using KTD): 1. For step 1, it should be export DBIC_TRACE=1 (not export DBICE_TRACE=1) (I amended the patch commit message). 2. Output before the patch: ... SELECT COUNT( * ) FROM `issues` `me` WHERE ( ( `borrowernumber` = ? AND ( `itemnumber` = ? OR `itemnumber` = ? OR `itemnumber` = ? OR `itemnumber` = ? ) ) ): '5', '52', '53', '54', '55' SELECT COUNT( * ) FROM `old_issues` `me` WHERE ( ( `borrowernumber` = ? AND ( `itemnumber` = ? OR `itemnumber` = ? OR `itemnumber` = ? OR `itemnumber` = ? ) ) ): '5', '52', '53', '54', '55' 3. Output after the patch: ... SELECT COUNT( * ) FROM `issues` `me` WHERE ( ( `borrowernumber` = ? AND `itemnumber` IN ( ?, ?, ?, ? ) ) ): '5', '52', '53', '54', '55' SELECT `me`.`variable`, `me`.`value`, `me`.`options`, `me`.`explanation`, `me`.`type` FROM `systempreferences` `me` WHERE ( `me`.`variable` = ? ): 'checkprevcheckoutdelay' SELECT COUNT( * ) FROM `old_issues` `me` WHERE ( ( `borrowernumber` = ? AND `itemnumber` IN ( ?, ?, ?, ? ) ) ): '5', '52', '53', '54', '55' -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39920 Martin Renvoize (ashimema) <martin.renvoize@openfifth.co.uk> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #182563|0 |1 is obsolete| | --- Comment #4 from Martin Renvoize (ashimema) <martin.renvoize@openfifth.co.uk> --- Created attachment 182744 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=182744&action=edit Bug 39920: Update query for check previous checkout to use IN This patch alters the query for checking previous checkouts to use IN instead of a list of OR for itemnumbers. To test: 1 - On the command line: export DBIC_TRACE=1 2 - perl -e 'use Koha::Patrons;my $patron = Koha::Patrons->find(5);my $item = Koha::Items->find(54);warn $item->itemnumber;$patron->do_check_for_previous_checkout( $item->unblessed );' 3 - Note the query 4 - Apply patch 5 - Repeat 2 6 - Note the shorter query Signed-off-by: David Nind <david@davidnind.com> Signed-off-by: Martin Renvoize <martin.renvoize@openfifth.co.uk> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39920 Martin Renvoize (ashimema) <martin.renvoize@openfifth.co.uk> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Signed Off |Passed QA QA Contact|testopia@bugs.koha-communit |martin.renvoize@openfifth.c |y.org |o.uk CC| |martin.renvoize@openfifth.c | |o.uk -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39920 Lucas Gass (lukeg) <lucas@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Passed QA |Pushed to main Version(s)| |25.11.00 released in| | -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39920 --- Comment #5 from Lucas Gass (lukeg) <lucas@bywatersolutions.com> --- Nice work everyone! Pushed to main for 25.11 -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39920 Fridolin Somers <fridolin.somers@biblibre.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Version(s)|25.11.00 |25.11.00,25.05.01 released in| | CC| |fridolin.somers@biblibre.co | |m Status|Pushed to main |Pushed to stable --- Comment #6 from Fridolin Somers <fridolin.somers@biblibre.com> --- I see in 25.05.x -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39920 Fridolin Somers <fridolin.somers@biblibre.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Version(s)|25.11.00,25.05.01 |25.11.00,25.05.01,24.11.06 released in| | Status|Pushed to stable |Pushed to oldstable --- Comment #7 from Fridolin Somers <fridolin.somers@biblibre.com> --- Pushed to 24.11.x for 24.11.06 -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39920 --- Comment #8 from Paul Derscheid <paul.derscheid@lmscloud.de> --- Nice work everyone! Pushed to 25.05.x for 25.05.03 -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39920 Jesse Maseto <jesse@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Pushed to oldstable |Pushed to oldoldstable CC| |jesse@bywatersolutions.com --- Comment #9 from Jesse Maseto <jesse@bywatersolutions.com> --- This will be pushed to 24.05.x for 24.05.12 -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39920 --- Comment #10 from Jesse Maseto <jesse@bywatersolutions.com> --- Sorry I updated this too early. Merge conflicts. Please resolve if needed for 24.05.x -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39920 Wainui Witika-Park <wainuiwitikapark@catalyst.net.nz> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Pushed to oldoldstable |Needs documenting CC| |wainuiwitikapark@catalyst.n | |et.nz --- Comment #11 from Wainui Witika-Park <wainuiwitikapark@catalyst.net.nz> --- Not backporting to 22.11.x as it wasn't backported to 24.05.x -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39920 David Nind <david@davidnind.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Resolution|--- |FIXED Status|Needs documenting |RESOLVED --- Comment #12 from David Nind <david@davidnind.com> --- Bug fix, no changes to the manual required. -- You are receiving this mail because: You are watching all bug changes.
participants (1)
-
bugzilla-daemon＠bugs.koha-community.org