Bugzilla – Attachment 15583 Details for
Bug 9681
Add some indexes in database
Home
|
New
|
Browse
|
Search
|
[?]
|
Reports
|
Help
|
New Account
|
Log In
[x]
|
Forgot Password
Login:
[x]
[patch]
Proposed patch
0001-Bug-9681-Add-some-indexes-in-database.patch (text/plain), 6.86 KB, created by
Fridolin Somers
on 2013-02-21 11:07:21 UTC
(
hide
)
Description:
Proposed patch
Filename:
MIME Type:
Creator:
Fridolin Somers
Created:
2013-02-21 11:07:21 UTC
Size:
6.86 KB
patch
obsolete
>From 693637337c33cd2ed5cc4f5ad153c5608a4c5f3d 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] 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) >--- > installer/data/mysql/kohastructure.sql | 22 +++++++++++++-- > installer/data/mysql/updatedatabase.pl | 46 ++++++++++++++++++++++++++++++++ > 2 files changed, 66 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 15d900c..b4f7163 100755 >--- a/installer/data/mysql/updatedatabase.pl >+++ b/installer/data/mysql/updatedatabase.pl >@@ -6407,6 +6407,52 @@ 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 (Some missing database indexes)\n"; >+ SetVersion($DBversion); >+} >+ > =head1 FUNCTIONS > > =head2 TableExists($table) >-- >1.7.10.4 >
You cannot view the attachment while viewing its details because your browser does not support IFRAMEs.
View the attachment on a separate page
.
View Attachment As Diff
View Attachment As Raw
Actions:
View
|
Diff
|
Splinter Review
Attachments on
bug 9681
:
15583
|
15590
|
15642
|
17959