[Bug 39618] New: Add a non-unique index/key to borrowers table for preferred_name
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39618 Bug ID: 39618 Summary: Add a non-unique index/key to borrowers table for preferred_name Change sponsored?: --- Product: Koha Version: Main Hardware: All OS: All Status: NEW Severity: enhancement Priority: P5 - low Component: Architecture, internals, and plumbing Assignee: koha-bugs@lists.koha-community.org Reporter: lucas@bywatersolutions.com QA Contact: testopia@bugs.koha-community.org We have seen some slowdown in patron searches since upgrading sites to 24.11 that seem to be tied to the introduction of preferred_name. Adding a key for preferred_name seems to speed up queries by quite a bit when filtering or sorting by preferred_name. This has been most notable in the patron autocomplete where queries went from over 5 seconds without, to .2 seconds with. -- 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=39618 Lucas Gass (lukeg) <lucas@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Patch complexity|--- |Trivial patch Assignee|koha-bugs@lists.koha-commun |lucas@bywatersolutions.com |ity.org | -- 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=39618 Lucas Gass (lukeg) <lucas@bywatersolutions.com> 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=39618 --- Comment #1 from Lucas Gass (lukeg) <lucas@bywatersolutions.com> --- Created attachment 180887 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=180887&action=edit Bug 39618: Add index to borrowers.preferred_name column To test: 1. APPLY PATCH 2. updatedatabase -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39618 Lucas Gass (lukeg) <lucas@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |nick@bywatersolutions.com -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39618 David Cook <dcook@prosentient.com.au> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Needs Signoff |Failed QA CC| |dcook@prosentient.com.au --- Comment #2 from David Cook <dcook@prosentient.com.au> --- That's really interesting. Can you explain the patch a bit more? Marking Failed QA since the patch is missing a change for kohastructure.sql. I double-checked main and it doesn't look like that index already exists there. Also, in the atomic update, it only adds the index if the "debit_id" index doesn't exist in the "article_requests" table? I suspect that must be a mistake? -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39618 Lucas Gass (lukeg) <lucas@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Failed QA |Needs Signoff -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39618 --- Comment #3 from Lucas Gass (lukeg) <lucas@bywatersolutions.com> --- Created attachment 180926 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=180926&action=edit Bug 39618: Add KEY to kohastructure.sql -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39618 --- Comment #4 from Lucas Gass (lukeg) <lucas@bywatersolutions.com> --- (In reply to David Cook from comment #2)
That's really interesting.
Can you explain the patch a bit more?
Without an index a search on the borrowers table for 'Bri', 'Bria', or 'Brian' will read through every row checking each 1 by 1. With an index the DB lookup starts with 'Bri', 'Bria', or 'Brian' as a starting point and is much faster. I mention 'Bri', 'Bria', or 'Brian' because these may all be lookups w/ the autocomplete feature as you continue to type the name 'Brian', letter by letter. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39618 --- Comment #5 from Nick Clemens (kidclamp) <nick@bywatersolutions.com> --- Essentially we found that if libraries add 'preferred_name' to DefaultPatronSearchFields system preference it slows down the sql queries SIMPLE EXAMPLE MariaDB [koha_kohadev]> SELECT COUNT(*) FROM borrowers WHERE preferred_name LIKE 'aaron%'; +----------+ | COUNT(*) | +----------+ | 992 | +----------+ 1 row in set (0.052 sec) MariaDB [koha_kohadev]> ALTER TABLE `borrowers` ADD KEY `preferred_name` (`preferred_name`); Query OK, 0 rows affected, 1 warning (0.155 sec) Records: 0 Duplicates: 0 Warnings: 1 MariaDB [koha_kohadev]> SELECT COUNT(*) FROM borrowers WHERE preferred_name LIKE 'aaron%'; +----------+ | COUNT(*) | +----------+ | 992 | +----------+ 1 row in set (0.002 sec) MariaDB [koha_kohadev]> In production with real Koha generated queries it went from several seconds per search to under 1 second per search -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39618 --- Comment #6 from David Cook <dcook@prosentient.com.au> --- Sorry folks. I understood what Lucas said in the Description. I just wanted to know if I was actually seeing mistakes in the first patch or if there was a reason the kohastructure.sql change was missing and the index_exists() was different to what I expected. I should've worded my comment differently. My bad! -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39618 --- Comment #7 from David Cook <dcook@prosentient.com.au> --- I love index related patches haha. Recently, on a different project, adding one (partial) index took a data heavy query down from 2 minutes to 1 second in some cases. I'll QA this later today once I've gotten through my emails. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39618 --- Comment #8 from Lucas Gass (lukeg) <lucas@bywatersolutions.com> --- (In reply to David Cook from comment #6)
Sorry folks. I understood what Lucas said in the Description. I just wanted to know if I was actually seeing mistakes in the first patch or if there was a reason the kohastructure.sql change was missing and the index_exists() was different to what I expected. I should've worded my comment differently. My bad!
Nope, that was a lazy mistake on my part. My bad! -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39618 David Cook <dcook@prosentient.com.au> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Needs Signoff |Failed QA --- Comment #9 from David Cook <dcook@prosentient.com.au> --- Oops I thought this was already signed off. I guess I meant sign off rather than QA. This is looking good at a glance. I do notice that it doesn't follow the pattern of most of the other indexes (ie it's missing the "_idx" suffix at the end of the index name), but that's fine I think. Not a real issue. My test plan: 0. Apply the patch 1. sudo koha-mysql kohadev 2. show indexes from borrowers 3. sudo koha-upgrade-schema kohadev NOTE successful database update 4. sudo koha-mysql kohadev 5. show indexes from borrowers NOTE indexes added to table 6. sudo koha-upgrade-schema kohadev NOTE It says it's added the index again (but it hasn't - phew) 7. sudo koha-mysql kohadev 8. show indexes from borrowers NOTE indexes hasn't been added a second time Sorry to say this but I think I am going to mark "Failed QA" again, as I think that "say_success" in the atomic update shouldn't be shown if the database update hasn't been applied. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39618 Nick Clemens (kidclamp) <nick@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Failed QA |Needs Signoff -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39618 Nick Clemens (kidclamp) <nick@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #180887|0 |1 is obsolete| | Attachment #180926|0 |1 is obsolete| | --- Comment #10 from Nick Clemens (kidclamp) <nick@bywatersolutions.com> --- Created attachment 181528 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=181528&action=edit Bug 39618: Add index to borrowers.preferred_name column To test: 1. APPLY PATCH 2. updatedatabase -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39618 Roman Dolny <roman.dolny@jezuici.pl> 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=39618 Roman Dolny <roman.dolny@jezuici.pl> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #181528|0 |1 is obsolete| | --- Comment #11 from Roman Dolny <roman.dolny@jezuici.pl> --- Created attachment 181555 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=181555&action=edit Bug 39618: Add index to borrowers.preferred_name column To test: 1. APPLY PATCH 2. updatedatabase Signed-off-by: Roman Dolny <roman.dolny@jezuici.pl> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39618 --- Comment #12 from Katrin Fischer <katrin.fischer@bsz-bw.de> --- I feel like this is bordering on a bugfix, would you agree? -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39618 Lucas Gass (lukeg) <lucas@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Assignee|lucas@bywatersolutions.com |nick@bywatersolutions.com -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39618 --- Comment #13 from Lucas Gass (lukeg) <lucas@bywatersolutions.com> --- (In reply to Katrin Fischer from comment #12)
I feel like this is bordering on a bugfix, would you agree?
Yes, I agree. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39618 Katrin Fischer <katrin.fischer@bsz-bw.de> changed: What |Removed |Added ---------------------------------------------------------------------------- Severity|enhancement |normal -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39618 Martin Renvoize (ashimema) <martin.renvoize@openfifth.co.uk> changed: What |Removed |Added ---------------------------------------------------------------------------- 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=39618 Martin Renvoize (ashimema) <martin.renvoize@openfifth.co.uk> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #181555|0 |1 is obsolete| | --- Comment #14 from Martin Renvoize (ashimema) <martin.renvoize@openfifth.co.uk> --- Created attachment 181872 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=181872&action=edit Bug 39618: Add index to borrowers.preferred_name column To test: 1. APPLY PATCH 2. updatedatabase Signed-off-by: Roman Dolny <roman.dolny@jezuici.pl> 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=39618 Martin Renvoize (ashimema) <martin.renvoize@openfifth.co.uk> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Signed Off |Passed QA -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39618 Katrin Fischer <katrin.fischer@bsz-bw.de> changed: What |Removed |Added ---------------------------------------------------------------------------- Version(s)| |25.05.00 released in| | Status|Passed QA |Pushed to main -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39618 --- Comment #15 from Katrin Fischer <katrin.fischer@bsz-bw.de> --- Pushed for 25.05! Well done everyone, thank you! -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39618 Adolfo Rodríguez Taboada <adolfo.rodriguez@xercode.es> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |adolfo.rodriguez@xercode.es --- Comment #16 from Adolfo Rodríguez Taboada <adolfo.rodriguez@xercode.es> --- This patch returned the following error in my local instance. ERROR: {UNKNOWN}: DBI Exception: DBD::mysql::db do failed: BLOB/TEXT column 'preferred_name' used in key specification without a key length The field preferred_name is defined as longtext so I understand the key for preferred_name should have a length like the index of middle_name CREATE INDEX middle_name_idx ON borrowers ( middle_name(768) ) -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39618 --- Comment #17 from Katrin Fischer <katrin.fischer@bsz-bw.de> --- Hi Adolfo, can you please tell us the OS, DBMS and version of your DBMS? I reckon there is some version difference at play here as it worked well in my tests. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39618 Katrin Fischer <katrin.fischer@bsz-bw.de> changed: What |Removed |Added ---------------------------------------------------------------------------- Keywords| |additional_work_needed -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39618 --- Comment #18 from Adolfo Rodríguez Taboada <adolfo.rodriguez@xercode.es> --- Hello, the operative system is Debian GNU/Linux 11 (bullseye) and the DBMS is 8.0.31 MySQL Community Server - GPL. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39618 --- Comment #19 from David Cook <dcook@prosentient.com.au> --- (In reply to Adolfo Rodríguez Taboada from comment #16)
This patch returned the following error in my local instance.
ERROR: {UNKNOWN}: DBI Exception: DBD::mysql::db do failed: BLOB/TEXT column 'preferred_name' used in key specification without a key length
The field preferred_name is defined as longtext so I understand the key for preferred_name should have a length like the index of middle_name
CREATE INDEX middle_name_idx ON borrowers ( middle_name(768) )
I think that Adolfo makes a good point. At the very least, we should be consistent in the adding of indexes, and middle name is a good one to copy. On a fairly new KTD, I dropped "preferred_name_idx" and re-added it. While there wasn't a noisy error message, I did notice that there was a warning in KTD: MariaDB [koha_kohadev]> ALTER TABLE `borrowers` ADD KEY `preferred_name_idx` (`preferred_name`); Query OK, 0 rows affected, 1 warning (0.050 sec) Records: 0 Duplicates: 0 Warnings: 1 MariaDB [koha_kohadev]> show warnings; +-------+------+----------------------------------------------------------+ | Level | Code | Message | +-------+------+----------------------------------------------------------+ | Note | 1071 | Specified key was too long; max key length is 3072 bytes | +-------+------+----------------------------------------------------------+ 1 row in set (0.000 sec) -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39618 --- Comment #20 from David Cook <dcook@prosentient.com.au> --- I just tried using the web installer and it hides the warning. -- In KTD we're using STRICT_TRANS_TABLES which should be activating strict mode. According to MySQL 8.4 website: "Strict mode produces an error for attempts to create a key that exceeds the maximum key length. When strict mode is not enabled, this results in a warning and truncation of the key to the maximum key length." -- I can't find any useful information on the MariaDB website about it. -- In any case, we should add a follow-up for this one to bring it in line with our other indexes and prevent issues for people. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39618 --- Comment #21 from David Cook <dcook@prosentient.com.au> --- Created attachment 182247 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=182247&action=edit Bug 39618: (QA follow-up) add prefix length to index key -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39618 --- Comment #22 from David Cook <dcook@prosentient.com.au> --- Hopefully I've added that right... Note you can use "show indexes from borrowers" to double-check the "Sub_part" length for the indexes. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39618 --- Comment #23 from Katrin Fischer <katrin.fischer@bsz-bw.de> --- Follow-up pushed to main. @David: can you explain about your choice of 768? -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39618 --- Comment #24 from David Cook <dcook@prosentient.com.au> --- (In reply to Katrin Fischer from comment #23)
Follow-up pushed to main.
@David: can you explain about your choice of 768?
It's two-fold. First, middle_name and other longtext column indexes are configured the same way. Second, it's what MySQL says to do: "For InnoDB tables that use COMPRESSED or DYNAMIC row format, index key prefixes longer than 767 bytes (up to 3072 bytes) are permitted. Tables created with these row formats enable you to index a maximum of 1024 or 768 characters for utf8mb3 or utf8mb4 columns, respectively." 768 characters at 4 bytes each = 3072 total bytes https://dev.mysql.com/doc/refman/8.4/en/charset-unicode-conversion.html -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39618 --- Comment #25 from David Cook <dcook@prosentient.com.au> --- Bug 28267 changed it so that we require DYNAMIC rows so 768 again makes the most sense I reckon. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39618 Katrin Fischer <katrin.fischer@bsz-bw.de> changed: What |Removed |Added ---------------------------------------------------------------------------- Keywords|additional_work_needed | --- Comment #26 from Katrin Fischer <katrin.fischer@bsz-bw.de> --- Follow-up pushed, explanation provided - removing keyword. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39618 ayoung <ayoung@oslri.net> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |ayoung@oslri.net --- Comment #27 from ayoung <ayoung@oslri.net> --- Would it be possible to backport to 24.11? Thanks! -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39618 Fridolin Somers <fridolin.somers@biblibre.com> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |fridolin.somers@biblibre.co | |m Version(s)|25.05.00 |25.05.00,24.11.10 released in| | Status|Pushed to main |Pushed to stable --- Comment #28 from Fridolin Somers <fridolin.somers@biblibre.com> --- Pushed to 24.11.x for 24.11.10 -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39618 Wainui Witika-Park <wainuiwitikapark@catalyst.net.nz> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Pushed to stable |Needs documenting --- Comment #29 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=39618 David Nind <david@davidnind.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Needs documenting |RESOLVED Resolution|--- |FIXED CC| |david@davidnind.com --- Comment #30 from David Nind <david@davidnind.com> --- Architecture related, 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