From 15aa1afa7559c61be7b6a9006e2d0464135ed5e4 Mon Sep 17 00:00:00 2001 From: Paul Poulain Date: Mon, 6 May 2013 15:06:47 +0200 Subject: [PATCH] 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 Comment: Indexes created as described. No errors. Fixing small merge conflict. Test presence of new indexes on all affected tables. Signed-off-by: Paul Poulain Signed-off-by: Jared Camins-Esakov (cherry picked from commit 68ff49bb7d982737c41a225cd697b8fb60d0b4ab) Signed-off-by: Jared Camins-Esakov --- 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 34a4cec723..eddfc3e446 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`), @@ -659,6 +660,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; @@ -793,7 +795,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; -- @@ -991,6 +994,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 @@ -1084,6 +1090,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 @@ -1448,6 +1455,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, @@ -1841,7 +1850,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 06909dfcbe..2bd043987e 100755 --- a/installer/data/mysql/updatedatabase.pl +++ b/installer/data/mysql/updatedatabase.pl @@ -6788,6 +6788,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 -- 2.39.5