Bug 39618 - Add a non-unique index/key to borrowers table for preferred_name
Summary: Add a non-unique index/key to borrowers table for preferred_name
Status: Needs Signoff
Alias: None
Product: Koha
Classification: Unclassified
Component: Architecture, internals, and plumbing (show other bugs)
Version: Main
Hardware: All All
: P5 - low enhancement
Assignee: Lucas Gass (lukeg)
QA Contact: Testopia
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2025-04-11 20:33 UTC by Lucas Gass (lukeg)
Modified: 2025-04-14 23:49 UTC (History)
2 users (show)

See Also:
Change sponsored?: ---
Patch complexity: Trivial patch
Documentation contact:
Documentation submission:
Text to go in the release notes:
Version(s) released in:
Circulation function:


Attachments
Bug 39618: Add index to borrowers.preferred_name column (1.40 KB, patch)
2025-04-11 20:42 UTC, Lucas Gass (lukeg)
Details | Diff | Splinter Review
Bug 39618: Add KEY to kohastructure.sql (1.82 KB, patch)
2025-04-14 18:12 UTC, Lucas Gass (lukeg)
Details | Diff | Splinter Review

Note You need to log in before you can comment on or make changes to this bug.
Description Lucas Gass (lukeg) 2025-04-11 20:33:22 UTC
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.
Comment 1 Lucas Gass (lukeg) 2025-04-11 20:42:18 UTC
Created attachment 180887 [details] [review]
Bug 39618: Add index to borrowers.preferred_name column

To test:
1. APPLY PATCH
2. updatedatabase
Comment 2 David Cook 2025-04-13 23:58:26 UTC
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?
Comment 3 Lucas Gass (lukeg) 2025-04-14 18:12:08 UTC
Created attachment 180926 [details] [review]
Bug 39618: Add KEY to kohastructure.sql
Comment 4 Lucas Gass (lukeg) 2025-04-14 18:20:47 UTC
(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.
Comment 5 Nick Clemens (kidclamp) 2025-04-14 18:26:23 UTC
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
Comment 6 David Cook 2025-04-14 22:54:03 UTC
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!
Comment 7 David Cook 2025-04-14 22:56:38 UTC
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.
Comment 8 Lucas Gass (lukeg) 2025-04-14 23:49:16 UTC
(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!