From b09af142effa37097984f327b59a629c7b1dd930 Mon Sep 17 00:00:00 2001 From: Chris Sharp Date: Mon, 5 Dec 2016 17:46:02 -0500 Subject: [PATCH] LP#1643932 - Fix search slowness when patron searching by username Bug 1501781 introduced a feature to improve patron name searching, but which was causing very slow queries when searching by username. Adding an index to username solves the slowness problem. Suggested by Mike Rylander. Signed-off-by: Chris Sharp Signed-off-by: Galen Charlton --- Open-ILS/src/sql/Pg/005.schema.actors.sql | 1 + .../XXXX.schema.actor_usr_usrname_unaccent_idx.sql | 8 ++++++++ 2 files changed, 9 insertions(+) create mode 100644 Open-ILS/src/sql/Pg/upgrade/XXXX.schema.actor_usr_usrname_unaccent_idx.sql diff --git a/Open-ILS/src/sql/Pg/005.schema.actors.sql b/Open-ILS/src/sql/Pg/005.schema.actors.sql index d44ac53cd6..90a351ddd0 100644 --- a/Open-ILS/src/sql/Pg/005.schema.actors.sql +++ b/Open-ILS/src/sql/Pg/005.schema.actors.sql @@ -85,6 +85,7 @@ CREATE INDEX actor_usr_family_name_idx ON actor.usr (evergreen.lowercase(family_ CREATE INDEX actor_usr_first_given_name_unaccent_idx ON actor.usr (evergreen.unaccent_and_squash(first_given_name)); CREATE INDEX actor_usr_second_given_name_unaccent_idx ON actor.usr (evergreen.unaccent_and_squash(second_given_name)); CREATE INDEX actor_usr_family_name_unaccent_idx ON actor.usr (evergreen.unaccent_and_squash(family_name)); +CREATE INDEX actor_usr_usrname_unaccent_idx ON actor.usr (evergreen.unaccent_and_squash(usrname)); CREATE INDEX actor_usr_usrname_idx ON actor.usr (evergreen.lowercase(usrname)); CREATE INDEX actor_usr_email_idx ON actor.usr (evergreen.lowercase(email)); diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.actor_usr_usrname_unaccent_idx.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.actor_usr_usrname_unaccent_idx.sql new file mode 100644 index 0000000000..5bf7167b47 --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.actor_usr_usrname_unaccent_idx.sql @@ -0,0 +1,8 @@ +BEGIN; + +SELECT evergreen.upgrade_deps_block_check('XXXX', :eg_version); -- csharp/miker + +CREATE INDEX actor_usr_usrname_unaccent_idx ON actor.usr (evergreen.unaccent_and_squash(usrname)); + + +COMMIT; -- 2.43.2