[Bug 42602] New: Patron search performance is poor if there are many patrons
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=42602 Bug ID: 42602 Summary: Patron search performance is poor if there are many patrons Initiative type: --- Sponsorship --- status: Product: Koha Version: Main Hardware: All OS: All Status: NEW Severity: enhancement Priority: P5 - low Component: Patrons Assignee: koha-bugs@lists.koha-community.org Reporter: pedro.amorim@openfifth.co.uk QA Contact: testopia@bugs.koha-community.org CC: gmcharlt@gmail.com, kyle@bywatersolutions.com -- 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 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 Pedro Amorim <pedro.amorim@openfifth.co.uk> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|NEW |Needs Signoff -- You are receiving this mail because: 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 #2 from Pedro Amorim <pedro.amorim@openfifth.co.uk> --- Created attachment 199150 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=199150&action=edit Bug 42602: Patron search prefetch tests prove t/Koha/REST/Plugin/Query.t prove t/db_dependent/Koha/REST/Plugin/Objects.t -- 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 #4 from Pedro Amorim <pedro.amorim@openfifth.co.uk> --- Created attachment 199152 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=199152&action=edit Bug 42602: Nuke defer_loading logic This only existed as a workaround to poor initial table load performance. We don't need this anymore. Some pages/modals now load directly patrons directly e.g. selecting manager for a suggestion, creating batch patron cards, and others: - members/memberentrygen.tt (add guarantor) - circ/request-article.tt - acqui/neworderempty.tt, acqui/basket.tt - serials/routing.tt - reserve/request.tt - suggestion/suggestion.tt - patroncards/edit-batch.tt - select_manager.inc -- 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 Tomás Cohen Arazi (tcohen) <tomascohen@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |tomascohen@gmail.com -- 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.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=42602 Martin Renvoize (ashimema) <martin.renvoize@openfifth.co.uk> changed: What |Removed |Added ---------------------------------------------------------------------------- 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=42602 David Nind <david@davidnind.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Needs Signoff |Patch doesn't apply CC| |david@davidnind.com --- Comment #6 from David Nind <david@davidnind.com> --- The patches no longer apply 8-(... 1. DON'T PUSH patch applied. 2. Rest of patches applied using interactive mode: Apply? [(y)es, (n)o, (i)nteractive] i ✓ Selected 4 patch(es): • Bug 42602: REST API search performance improvements • Bug 42602: Patron search prefetch tests • Bug 42602: Update datatables library filter column • Bug 42602: Nuke defer_loading logic Preparing 4 patch(es): ✓ Prepared 4 patch(es) Applying 4 patch(es): Patch application failed for attachment 199149 - Bug 42602: REST API search performance improvements -- You are receiving this mail because: You are watching all bug changes.
participants (1)
-
bugzilla-daemon＠bugs.koha-community.org