From 73c08ceb56c1f707ab367375b3ea9e3a57823ea6 Mon Sep 17 00:00:00 2001 From: Galen Charlton Date: Fri, 12 Jul 2013 15:10:32 -0400 Subject: [PATCH] LP#1200735: improve performance of patron search by username This patch adds an additional index on actor.usr.usrname. Signed-off-by: Galen Charlton Signed-off-by: Bill Erickson --- Open-ILS/src/sql/Pg/005.schema.actors.sql | 1 + Open-ILS/src/sql/Pg/upgrade/XXXX.schema.usrname_index.sql | 11 +++++++++++ 2 files changed, 12 insertions(+) create mode 100644 Open-ILS/src/sql/Pg/upgrade/XXXX.schema.usrname_index.sql diff --git a/Open-ILS/src/sql/Pg/005.schema.actors.sql b/Open-ILS/src/sql/Pg/005.schema.actors.sql index 86958525ce..8b6ff3864c 100644 --- a/Open-ILS/src/sql/Pg/005.schema.actors.sql +++ b/Open-ILS/src/sql/Pg/005.schema.actors.sql @@ -83,6 +83,7 @@ CREATE INDEX actor_usr_first_given_name_idx ON actor.usr (evergreen.lowercase(fi CREATE INDEX actor_usr_second_given_name_idx ON actor.usr (evergreen.lowercase(second_given_name)); CREATE INDEX actor_usr_family_name_idx ON actor.usr (evergreen.lowercase(family_name)); +CREATE INDEX actor_usr_usrname_idx ON actor.usr (evergreen.lowercase(usrname)); CREATE INDEX actor_usr_email_idx ON actor.usr (evergreen.lowercase(email)); CREATE INDEX actor_usr_day_phone_idx ON actor.usr (evergreen.lowercase(day_phone)); diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.usrname_index.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.usrname_index.sql new file mode 100644 index 0000000000..288b43af51 --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.usrname_index.sql @@ -0,0 +1,11 @@ +-- Evergreen DB patch XXXX.schema.usrname_index.sql +-- +-- Create search index on actor.usr.usrname +-- +BEGIN; + +SELECT evergreen.upgrade_deps_block_check('XXXX', :eg_version); + +CREATE INDEX actor_usr_usrname_idx ON actor.usr (evergreen.lowercase(usrname)); + +COMMIT; -- 2.11.0