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
Created attachment 182528 [details] [review] 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
Created attachment 182563 [details] [review] 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>
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'
Created attachment 182744 [details] [review] 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>
Nice work everyone! Pushed to main for 25.11
I see in 25.05.x
Pushed to 24.11.x for 24.11.06
Nice work everyone! Pushed to 25.05.x for 25.05.03