https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=42602 Pedro Amorim <pedro.amorim@openfifth.co.uk> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |pedro.amorim@openfifth.co.u | |k Assignee|koha-bugs@lists.koha-commun |pedro.amorim@openfifth.co.u |ity.org |k -- 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=42602 --- Comment #1 from Pedro Amorim <pedro.amorim@openfifth.co.uk> --- Created attachment 199149 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=199149&action=edit Bug 42602: REST API search performance improvements belongs_to prefetch is now skipped when related table not referenced in filter or sort A belongs_to embed adds a LEFT JOIN to the main SELECT. MySQL can normally satisfy ORDER BY + LIMIT 20 by walking the sort index and stopping early, but a LEFT JOIN forces a filesort across all matching rows first. On large datasets (e.g. 250k+ patrons) this is significant. When a belongs_to relation is not referenced in the filter or sort parameters, it is now skipped from prefetch. In other words, a LEFT JOIN is only added to the query if a filter or sort on that table exists. Otherwise, the LEFT JOIN is not added. -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=42602 --- Comment #3 from Pedro Amorim <pedro.amorim@openfifth.co.uk> --- Created attachment 199151 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=199151&action=edit Bug 42602: Update datatables library filter column The Datatables column data was set to library.name:me.library_id, causing every library filter to query both fields. This forced a LEFT JOIN on branches even though the filter value is always a branchcode, a column already on the patrons table. Dropping library.name from the data field avoids the JOIN entirely. -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=42602 --- Comment #5 from Pedro Amorim <pedro.amorim@openfifth.co.uk> --- Created attachment 199153 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=199153&action=edit Bug 42602: [DONT PUSH] Test only Test plan, apply this patch only: 1) Create 500k patrons, (k-t-d) run: perl manypatrons.pl 2) Access the patron search page: <staff_url>/cgi-bin/koha/members/members-home.pl 3) Click 'Search'. Notice it takes a few seconds (mileage will vary, for me it takes ~10 seconds on my local setup). 4) Apply the other patches. Repeat 3). Notice it should resolve now in a few (3) seconds max. 5) Optionally, run the benchmark test script: perl showsql.pl -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=42602 David Cook <dcook@prosentient.com.au> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |dcook@prosentient.com.au -- You are receiving this mail because: You are watching all bug changes.