Attachment 23001 Details for Bug 11249 – Bug 11249 - Add db indexes on borrowers names

[patch] Bug 11249 - Add db indexes on borrowers names

0001-Bug-11249-Add-db-indexes-on-borrowers-names.patch (text/plain), 2.90 KB, created by Fridolin Somers on 2013-11-18 16:11:14 UTC

(hide)

Description:

Filename:

MIME Type:

Creator: Fridolin Somers

Created: 2013-11-18 16:11:14 UTC

Size: 2.90 KB

patch

obsolete

>From a744aca65fea114d61964c4da9bd48824854bcdb Mon Sep 17 00:00:00 2001
>From: Fridolyn SOMERS <fridolyn.somers@biblibre.com>
>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).
>Addind db indexes will really increase the query speed.
>
>This patch adds bd 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
>---
> installer/data/mysql/kohastructure.sql |  3 +++
> installer/data/mysql/updatedatabase.pl | 11 +++++++++++
> 2 files changed, 14 insertions(+)
>
>diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql
>index fefc985..d394adc 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 db9bf95..acfdd0f 100755
>--- a/installer/data/mysql/updatedatabase.pl
>+++ b/installer/data/mysql/updatedatabase.pl
>@@ -7750,6 +7750,17 @@ if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) {
>     SetVersion($DBversion);
> }
> 
>+$DBversion = "3.13.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
> 
>-- 
>1.8.3.2
>

Actions: View | Diff | Splinter Review

Attachments on bug 11249: 22930 | 23001 | 23223 | 23262 | 74372