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.
Created attachment 180887 [details] [review] Bug 39618: Add index to borrowers.preferred_name column To test: 1. APPLY PATCH 2. updatedatabase
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?
Created attachment 180926 [details] [review] Bug 39618: Add KEY to kohastructure.sql
(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.
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
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!
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.
(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!