Bugzilla – Attachment 39720 Details for
Bug 14053
Acquisition db tables are missing indexes
Home
|
New
|
Browse
|
Search
|
[?]
|
Reports
|
Help
|
New Account
|
Log In
[x]
|
Forgot Password
Login:
[x]
[patch]
[PASSED QA] Bug 14053 - Acquisition db tables are missing indexes
PASSED-QA-Bug-14053---Acquisition-db-tables-are-mi.patch (text/plain), 4.76 KB, created by
Katrin Fischer
on 2015-05-31 20:55:24 UTC
(
hide
)
Description:
[PASSED QA] Bug 14053 - Acquisition db tables are missing indexes
Filename:
MIME Type:
Creator:
Katrin Fischer
Created:
2015-05-31 20:55:24 UTC
Size:
4.76 KB
patch
obsolete
>From 61eb7577c8d27eff249a6fbf8f0433d63c038e5c Mon Sep 17 00:00:00 2001 >From: Fridolin Somers <fridolin.somers@biblibre.com> >Date: Thu, 23 Apr 2015 17:38:03 +0200 >Subject: [PATCH] [PASSED QA] Bug 14053 - Acquisition db tables are missing > indexes > >Acquisition db tables are missing some indexes to have >performance queries. > >This patch adds an index on some columns very often >used in search queries, such as aqbooksellers.name and >aqbudgets.budget_code. > >Also adds an index on aqorders.orderstatus, very often used >with hardcoded value like 'cancelled', in various queries. > >Test plan : >1) Back up database >2) $ git reset --hard origin/master >3) $ git bz apply 14053 >4) In your mysql client > > DROP DATABASE koha_library; > > CREATE DATABASE koha_library; > > QUIT; > -- Obviously you may need to vary koha_library :) >5) Navigate to staff client > -- should be able to set up the DB just fine. > -- this will catch the comma bug that keeps coming in. >6) $ git reset --hard origin/master >7) Repeat step 4 >8) Navigate to staff client > -- nothing tested, but we need the DB set up. >9) $ ./installer/data/mysql/updatedatabase.pl > -- atomic updates run without issue. >10) run koha qa test tools >11) Restore DB >12) Try to compare performance after and before > database update. I think query contained in > C4::Acquistion::GetInvoices could be a good example > >Signed-off-by: Mark Tompsett <mtompset@hotmail.com> > >Signed-off-by: Katrin Fischer <katrin.fischer.83@web.de> >--- > installer/data/mysql/kohastructure.sql | 12 +++++++++++- > 1 file changed, 11 insertions(+), 1 deletion(-) > >diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql >index c41710c..6683125 100644 >--- a/installer/data/mysql/kohastructure.sql >+++ b/installer/data/mysql/kohastructure.sql >@@ -2853,6 +2853,7 @@ CREATE TABLE `aqbasket` ( -- stores data about baskets in acquisitions > KEY `booksellerid` (`booksellerid`), > KEY `basketgroupid` (`basketgroupid`), > KEY `contractnumber` (`contractnumber`), >+ KEY `authorisedby` (`authorisedby`), > CONSTRAINT `aqbasket_ibfk_1` FOREIGN KEY (`booksellerid`) REFERENCES `aqbooksellers` (`id`) ON UPDATE CASCADE, > CONSTRAINT `aqbasket_ibfk_2` FOREIGN KEY (`contractnumber`) REFERENCES `aqcontract` (`contractnumber`), > CONSTRAINT `aqbasket_ibfk_3` FOREIGN KEY (`basketgroupid`) REFERENCES `aqbasketgroups` (`id`) ON UPDATE CASCADE, >@@ -2907,6 +2908,7 @@ CREATE TABLE `aqbooksellers` ( -- information about the vendors listed in acquis > PRIMARY KEY (`id`), > KEY `listprice` (`listprice`), > KEY `invoiceprice` (`invoiceprice`), >+ KEY `name` (`name`(255)), > CONSTRAINT `aqbooksellers_ibfk_1` FOREIGN KEY (`listprice`) REFERENCES `currency` (`currency`) ON DELETE CASCADE ON UPDATE CASCADE, > CONSTRAINT `aqbooksellers_ibfk_2` FOREIGN KEY (`invoiceprice`) REFERENCES `currency` (`currency`) ON DELETE CASCADE ON UPDATE CASCADE > ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; >@@ -2932,7 +2934,12 @@ CREATE TABLE `aqbudgets` ( -- information related to Funds > `sort2_authcat` varchar(80) default NULL, -- second statistical category for this fund > `budget_owner_id` int(11) default NULL, -- borrowernumber of the person who owns this fund (borrowers.borrowernumber) > `budget_permission` int(1) default '0', -- level of permission for this fund (used only by the owner, only by the library, or anyone) >- PRIMARY KEY (`budget_id`) >+ PRIMARY KEY (`budget_id`), >+ KEY `budget_parent_id` (`budget_parent_id`), >+ KEY `budget_code` (`budget_code`), >+ KEY `budget_branchcode` (`budget_branchcode`), >+ KEY `budget_period_id` (`budget_period_id`), >+ KEY `budget_owner_id` (`budget_owner_id`) > ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; > > -- >@@ -2985,6 +2992,7 @@ CREATE TABLE `aqbudgets_planning` ( > `authvalue` varchar(30) NOT NULL, > `display` tinyint(1) DEFAULT 1, > PRIMARY KEY (`plan_id`), >+ KEY `budget_period_id` (`budget_period_id`), > CONSTRAINT `aqbudgets_planning_ifbk_1` FOREIGN KEY (`budget_id`) REFERENCES `aqbudgets` (`budget_id`) ON DELETE CASCADE ON UPDATE CASCADE > ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; > >@@ -3074,6 +3082,8 @@ CREATE TABLE `aqorders` ( -- information related to the basket line items > KEY `basketno` (`basketno`), > KEY `biblionumber` (`biblionumber`), > KEY `budget_id` (`budget_id`), >+ KEY `parent_ordernumber` (`parent_ordernumber`), >+ KEY `orderstatus` (`orderstatus`), > CONSTRAINT `aqorders_budget_id_fk` FOREIGN KEY (`budget_id`) REFERENCES `aqbudgets` (`budget_id`) ON DELETE CASCADE ON UPDATE CASCADE, > CONSTRAINT `aqorders_ibfk_1` FOREIGN KEY (`basketno`) REFERENCES `aqbasket` (`basketno`) ON DELETE CASCADE ON UPDATE CASCADE, > CONSTRAINT `aqorders_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE SET NULL ON UPDATE CASCADE, >-- >1.9.1
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 14053
:
38568
|
38569
|
38728
|
38729
|
38808
|
39135
|
39136
|
39153
|
39154
|
39155
|
39719
| 39720