https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=31222 --- Comment #1 from Nick Clemens <nick@bywatersolutions.com> --- Created attachment 138038 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=138038&action=edit Bug 31222: Reduce query size for batchMod The object search is currently: Koha::Items->search({ barcode => \@contentlist } Which generate code like: barcode = 1 OR barcode = 2 OR barcode = 3 .... This can get quite large We can reduce the query size by using -in: Koha::Items->search({ barcode => { -in => \@contentlist } } Which generates code like: barcode in ( 1, 2, 3 ) To test: 1 - Apply patch 2 - Perform batch modifications 3 - Confirm nothing has changed -- 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=31222 Nick Clemens <nick@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Failed QA |Needs Signoff --- Comment #3 from Nick Clemens <nick@bywatersolutions.com> --- (In reply to Rachael from comment #2)

Batch modifications changes each item. Ex. Modifying the Source of classification or shelving scheme to Dewey Decimal Classification gets applied.

Hi Rachel, I am not sure what you mean here? Batch modification should update each item, is it updating more fields than intended? -- 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=31222 --- Comment #5 from Nick Clemens <nick@bywatersolutions.com> --- (In reply to Rachael from comment #4)

(In reply to Nick Clemens from comment #3)

...

(In reply to Rachael from comment #2)

...

Batch modifications changes each item. Ex. Modifying the Source of classification or shelving scheme to Dewey Decimal Classification gets applied.

Hi Rachel, I am not sure what you mean here? Batch modification should update each item, is it updating more fields than intended?

Hi Nick, No, nothing else changes, just the fields that I specified. Should I sign it off then?

If everything works as expected with the patch, yes, please :-) -- 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=31222 Rachael <rachael.laritz@inlibro.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #138038|0 |1 is obsolete| | --- Comment #6 from Rachael <rachael.laritz@inlibro.com> --- Created attachment 138737 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=138737&action=edit Bug 31222: Reduce query size for batchMod The object search is currently: Koha::Items->search({ barcode => \@contentlist } Which generate code like: barcode = 1 OR barcode = 2 OR barcode = 3 .... This can get quite large We can reduce the query size by using -in: Koha::Items->search({ barcode => { -in => \@contentlist } } Which generates code like: barcode in ( 1, 2, 3 ) To test: 1 - Apply patch 2 - Perform batch modifications 3 - Confirm nothing has changed Signed-off-by: Rachael Laritz <rachael.laritz@inlibro.com> -- 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=31222 Jonathan Druart <jonathan.druart+koha@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |jonathan.druart+koha@gmail. | |com --- Comment #7 from Jonathan Druart <jonathan.druart+koha@gmail.com> --- Hum, what was the thing with the IN limit? -- 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=31222 Jonathan Druart <jonathan.druart+koha@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Assignee|koha-bugs@lists.koha-commun |nick@bywatersolutions.com |ity.org | -- 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=31222 Jonathan Druart <jonathan.druart+koha@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Signed Off |In Discussion --- Comment #10 from Jonathan Druart <jonathan.druart+koha@gmail.com> --- Waiting for a reply. -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=31222 --- Comment #12 from Jonathan Druart <jonathan.druart+koha@gmail.com> --- (In reply to Nick Clemens from comment #11)

It seems IN is more performant as well: https://mariadb.com/kb/en/in/

That's the correct argument ;) -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=31222 Jonathan Druart <jonathan.druart+koha@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #138737|0 |1 is obsolete| | --- Comment #13 from Jonathan Druart <jonathan.druart+koha@gmail.com> --- Created attachment 139293 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=139293&action=edit Bug 31222: Reduce query size for batchMod The object search is currently: Koha::Items->search({ barcode => \@contentlist } Which generate code like: barcode = 1 OR barcode = 2 OR barcode = 3 .... This can get quite large We can reduce the query size by using -in: Koha::Items->search({ barcode => { -in => \@contentlist } } Which generates code like: barcode in ( 1, 2, 3 ) To test: 1 - Apply patch 2 - Perform batch modifications 3 - Confirm nothing has changed Signed-off-by: Rachael Laritz <rachael.laritz@inlibro.com> Signed-off-by: Jonathan Druart <jonathan.druart@bugs.koha-community.org> -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=31222 Tomás Cohen Arazi <tomascohen@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Version(s)| |22.11.00 released in| | Status|Passed QA |Pushed to master -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=31222 David Cook <dcook@prosentient.com.au> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |dcook@prosentient.com.au --- Comment #16 from David Cook <dcook@prosentient.com.au> --- (In reply to Jonathan Druart from comment #8)

IN limit is max_allowed_packet, which is 16M by default.

(In reply to Jonathan Druart from comment #9)

Why do we want to reduce mysql query length exactly? You are not supposed to have mysql logging in production servers, you will face perf issues (but I guess you know that already).

While I don't think I've seen it in Koha, I have seen other systems throw MySQL errors, because their SQL queries were too long. Of course "barcode in(?, ?, ?)" could still have that happen, but I suppose it's less likely than "barcode = ? or barcode = ? or barcode = ?". -- I keep looking at https://mariadb.com/kb/en/in/, but it doesn't really make sense outside their limited examples. In the example 'SELECT 2 IN (0,3,5,7);' it makes sense that it would do a binary search of that value list, since that would be the most efficient operation. It would just need to do 1 search. However, if it's "SELECT barcode WHERE barcode IN (1,2,3,4,5)", then it would be extremely inefficient to binary search the value list, because it would have to row scan the whole table for each "barcode" field and then binary search the value list. At a glance, it looks like both query styles actually have the same performance: analyze select * from items where barcode = '1' or barcode = '2' or barcode = '3' or barcode = '4' or barcode = '5'; analyze select * from items where barcode in ('1','2','3','4','5'); Both queries do a range query using the itembarcodeidx. Now that idea of the database row scanning the whole table and then binary searching the value list can still happen, if the number of rows in the database is < the number of values in the value list. For example: analyze select * from z3950servers where id in ('1','2','3','4','5'); That will do an ALL type query (ie table scan of every row). -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=31222 Victor Grousset/tuxayo <victor@tuxayo.net> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Pushed to stable |RESOLVED Resolution|--- |FIXED CC| |victor@tuxayo.net --- Comment #18 from Victor Grousset/tuxayo <victor@tuxayo.net> --- Not backported to oldoldstable (21.05.x). Feel free to ask if it's needed. Nothing to document it seems, marking resolved. -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=31222 --- Comment #19 from Arthur Suzuki <arthur.suzuki@biblibre.com> --- Thanks! Pushed to 21.11 for 21.11.12 -- You are receiving this mail because: You are watching all bug changes.