From 19b5c9b4cecf935c1cf17649796debe80ae7b3a8 Mon Sep 17 00:00:00 2001 From: Fridolyn SOMERS Date: Thu, 14 Nov 2013 14:29:51 +0100 Subject: [PATCH] Bug 11249: Add Db indexes on borrowers names The borrowers search is by default on columns surname, firstname, othernames and cardnumber. (See C4::Members::_express_member_find). Adding DB indexes will really increase the query speed. This patch adds DB indexes on surname, firstname, othernames (cardnumber has already an index). Those indexes must be defined with a size because columns are mediumtext. Test plan : Test with mysql client : mysql> explain select * from borrowers where surname like 'A%'; +----+-------------+-----------+-------+---------------+-------------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-----------+-------+---------------+-------------+---------+------+------+-------------+ | 1 | SIMPLE | borrowers | range | surname_idx | surname_idx | 767 | NULL | 395 | Using where | +----+-------------+-----------+-------+---------------+-------------+---------+------+------+-------------+ => key show the index is used Signed-off-by: Mathieu Saby Signed-off-by: Katrin Fischer Works as described, changes from updatedatabase and in kohastructure match. I think deletedborrowers can be left out, as it's not queried when doing patron searches. Patron deletes still work as expected. Signed-off-by: Galen Charlton --- installer/data/mysql/kohastructure.sql | 3 +++ installer/data/mysql/updatedatabase.pl | 12 ++++++++++++ 2 files changed, 15 insertions(+) diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index 07160c8cee..e60b61e553 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -271,6 +271,9 @@ CREATE TABLE `borrowers` ( -- this table includes information about your patrons KEY `branchcode` (`branchcode`), KEY `userid` (`userid`), KEY `guarantorid` (`guarantorid`), + KEY `surname_idx` (`surname`(255)), + KEY `firstname_idx` (`firstname`(255)), + KEY `othernames_idx` (`othernames`(255)), CONSTRAINT `borrowers_ibfk_1` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`), CONSTRAINT `borrowers_ibfk_2` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; diff --git a/installer/data/mysql/updatedatabase.pl b/installer/data/mysql/updatedatabase.pl index d099a48936..c2f132e1df 100755 --- a/installer/data/mysql/updatedatabase.pl +++ b/installer/data/mysql/updatedatabase.pl @@ -7831,6 +7831,18 @@ if ( CheckVersion($DBversion) ) { SetVersion ($DBversion); } +$DBversion = "3.15.00.XXX"; +if(CheckVersion($DBversion)) { + $dbh->do(q{ + ALTER TABLE `borrowers` + ADD KEY `surname_idx` (`surname`(255)), + ADD KEY `firstname_idx` (`firstname`(255)), + ADD KEY `othernames_idx` (`othernames`(255)) + }); + print "Upgrade to $DBversion done (Bug 11249 - Add db indexes on borrowers names)\n"; + SetVersion($DBversion); +} + =head1 FUNCTIONS =head2 TableExists($table) -- 2.39.5