From d8e9f64c5dea008461c57216f5fcf7d458db60c7 Mon Sep 17 00:00:00 2001
From: Fridolyn SOMERS <fridolyn.somers@biblibre.com>
Date: Thu, 21 Feb 2013 11:58:59 +0100
Subject: [PATCH] [SIGNED-OFF] Bug 9681: Add some indexes in database
Some table columns should have an index to speed SQL queries :
- statistics table has only one index (used to make heavy queries for reports)
- itemtype should have an index in items and biblioitems (this is used in circulation to check the number of existing issues of a specific item type)
- issue.branchcode and issue.issuingbranch
Test plan :
- Create a new database using kohastructure.pl
=> check there is no SQL error and that new indexes are present (mysql> show create table)
Apply update database on a master version
=> check there is no SQL error and that new indexes are present (mysql> show create table)
Signed-off-by: Bernardo Gonzalez Kriegel <bgkriegel@gmail.com>
Comment: Indexes created as described. No errors.
Fixing small merge conflict.
Test presence of new indexes on all affected tables.
---
installer/data/mysql/kohastructure.sql | 22 ++++++++++++++--
installer/data/mysql/updatedatabase.pl | 45 ++++++++++++++++++++++++++++++++
2 files changed, 65 insertions(+), 2 deletions(-)
diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql
index ed673da..f3264e6 100644
--- a/installer/data/mysql/kohastructure.sql
+++ b/installer/data/mysql/kohastructure.sql
@@ -184,6 +184,7 @@ CREATE TABLE `biblioitems` ( -- information related to bibliographic records in
PRIMARY KEY (`biblioitemnumber`),
KEY `bibinoidx` (`biblioitemnumber`),
KEY `bibnoidx` (`biblionumber`),
+ KEY `itemtype_idx` (`itemtype`),
KEY `isbn` (`isbn`),
KEY `issn` (`issn`),
KEY `publishercode` (`publishercode`),
@@ -657,6 +658,7 @@ CREATE TABLE `deletedbiblioitems` ( -- information about bibliographic records t
PRIMARY KEY (`biblioitemnumber`),
KEY `bibinoidx` (`biblioitemnumber`),
KEY `bibnoidx` (`biblionumber`),
+ KEY `itemtype_idx` (`itemtype`),
KEY `isbn` (`isbn`),
KEY `publishercode` (`publishercode`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
@@ -791,7 +793,8 @@ CREATE TABLE `deleteditems` (
KEY `delitembinoidx` (`biblioitemnumber`),
KEY `delitembibnoidx` (`biblionumber`),
KEY `delhomebranch` (`homebranch`),
- KEY `delholdingbranch` (`holdingbranch`)
+ KEY `delholdingbranch` (`holdingbranch`),
+ KEY `itype_idx` (`itype`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
@@ -988,6 +991,9 @@ CREATE TABLE `issues` ( -- information related to check outs or issues
`timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- the date and time this record was last touched
`issuedate` datetime default NULL, -- date the item was checked out or issued
KEY `issuesborridx` (`borrowernumber`),
+ KEY `itemnumber_idx` (`itemnumber`),
+ KEY `branchcode_idx` (`branchcode`),
+ KEY `issuingbranch_idx` (`issuingbranch`),
KEY `bordate` (`borrowernumber`,`timestamp`),
CONSTRAINT `issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE RESTRICT ON UPDATE CASCADE,
CONSTRAINT `issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE RESTRICT ON UPDATE CASCADE
@@ -1080,6 +1086,7 @@ CREATE TABLE `items` ( -- holdings/item information
KEY `itemcallnumber` (`itemcallnumber`),
KEY `items_location` (`location`),
KEY `items_ccode` (`ccode`),
+ KEY `itype_idx` (`itype`),
CONSTRAINT `items_ibfk_1` FOREIGN KEY (`biblioitemnumber`) REFERENCES `biblioitems` (`biblioitemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `items_ibfk_2` FOREIGN KEY (`homebranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE,
CONSTRAINT `items_ibfk_3` FOREIGN KEY (`holdingbranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE
@@ -1458,6 +1465,8 @@ CREATE TABLE `old_issues` ( -- lists items that were checked out and have been r
`issuedate` datetime default NULL, -- date the item was checked out or issued
KEY `old_issuesborridx` (`borrowernumber`),
KEY `old_issuesitemidx` (`itemnumber`),
+ KEY `branchcode_idx` (`branchcode`),
+ KEY `issuingbranch_idx` (`issuingbranch`),
KEY `old_bordate` (`borrowernumber`,`timestamp`),
CONSTRAINT `old_issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
ON DELETE SET NULL ON UPDATE SET NULL,
@@ -1851,7 +1860,16 @@ CREATE TABLE `statistics` ( -- information related to transactions (circulation
`borrowernumber` int(11) default NULL, -- foreign key from the borrowers table, links transaction to a specific borrower
`associatedborrower` int(11) default NULL,
`ccode` varchar(10) default NULL, -- foreign key from the items table, links transaction to a specific collection code
- KEY `timeidx` (`datetime`)
+ KEY `timeidx` (`datetime`),
+ KEY `branch_idx` (`branch`),
+ KEY `proccode_idx` (`proccode`),
+ KEY `type_idx` (`type`),
+ KEY `usercode_idx` (`usercode`),
+ KEY `itemnumber_idx` (`itemnumber`),
+ KEY `itemtype_idx` (`itemtype`),
+ KEY `borrowernumber_idx` (`borrowernumber`),
+ KEY `associatedborrower_idx` (`associatedborrower`),
+ KEY `ccode_idx` (`ccode`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
diff --git a/installer/data/mysql/updatedatabase.pl b/installer/data/mysql/updatedatabase.pl
index 2108365..e239d21 100755
--- a/installer/data/mysql/updatedatabase.pl
+++ b/installer/data/mysql/updatedatabase.pl
@@ -6428,6 +6428,51 @@ if ( CheckVersion($DBversion) ) {
SetVersion($DBversion);
}
+$DBversion = "3.11.00.XXX";
+if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
+ #issues
+ $dbh->do(q{
+ ALTER TABLE `issues`
+ ADD KEY `itemnumber_idx` (`itemnumber`),
+ ADD KEY `branchcode_idx` (`branchcode`),
+ ADD KEY `issuingbranch_idx` (`issuingbranch`)
+ });
+ $dbh->do(q{
+ ALTER TABLE `old_issues`
+ ADD KEY `branchcode_idx` (`branchcode`),
+ ADD KEY `issuingbranch_idx` (`issuingbranch`)
+ });
+ #items
+ $dbh->do(q{
+ ALTER TABLE `items` ADD KEY `itype_idx` (`itype`)
+ });
+ $dbh->do(q{
+ ALTER TABLE `deleteditems` ADD KEY `itype_idx` (`itype`)
+ });
+ # biblioitems
+ $dbh->do(q{
+ ALTER TABLE `biblioitems` ADD KEY `itemtype_idx` (`itemtype`)
+ });
+ $dbh->do(q{
+ ALTER TABLE `deletedbiblioitems` ADD KEY `itemtype_idx` (`itemtype`)
+ });
+ # statistics
+ $dbh->do(q{
+ ALTER TABLE `statistics`
+ ADD KEY `branch_idx` (`branch`),
+ ADD KEY `proccode_idx` (`proccode`),
+ ADD KEY `type_idx` (`type`),
+ ADD KEY `usercode_idx` (`usercode`),
+ ADD KEY `itemnumber_idx` (`itemnumber`),
+ ADD KEY `itemtype_idx` (`itemtype`),
+ ADD KEY `borrowernumber_idx` (`borrowernumber`),
+ ADD KEY `associatedborrower_idx` (`associatedborrower`),
+ ADD KEY `ccode_idx` (`ccode`)
+ });
+
+ print "Upgrade to $DBversion done (Bug 9681: Add some database indexes)\n";
+ SetVersion($DBversion);
+}
=head1 FUNCTIONS
--
1.7.9.5