From 8ddb3135758cb5f2a1a4e12ea2d66540157228f7 Mon Sep 17 00:00:00 2001 From: Fridolin Somers Date: Thu, 23 Apr 2015 17:38:03 +0200 Subject: [PATCH] Bug 14053 - Acquisition db tables are missing indexes Acquisition db tables have a lot of relational constraints and are often missing an index to have performance queries. This patch adds an index for all columns used in a forein key. Also adds an index on some columns very often used in search queries : 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 : - Check SQL syntax is correct - Try to compare performance after and before database update. I think query contained in C4::Acquistion::GetInvoices could be a good example --- installer/data/mysql/kohastructure.sql | 20 +++++++++++++++++++- 1 file changed, 19 insertions(+), 1 deletion(-) diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index eb73cc5..0c2a9e2 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -2848,6 +2848,8 @@ CREATE TABLE `aqbasket` ( -- stores data about baskets in acquisitions KEY `booksellerid` (`booksellerid`), KEY `basketgroupid` (`basketgroupid`), KEY `contractnumber` (`contractnumber`), + KEY `authorisedby` (`authorisedby`), + KEY `branch` (`branch`), 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, @@ -2902,6 +2904,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; @@ -2927,7 +2930,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; -- @@ -2980,6 +2988,8 @@ CREATE TABLE `aqbudgets_planning` ( `authvalue` varchar(30) NOT NULL, `display` tinyint(1) DEFAULT 1, PRIMARY KEY (`plan_id`), + KEY `budget_id` (`budget_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; @@ -3003,6 +3013,7 @@ CREATE TABLE aqcontacts ( serialsprimary BOOLEAN NOT NULL DEFAULT 0, -- is this the primary contact for serials messages booksellerid int(11) not NULL, PRIMARY KEY (id), + KEY `booksellerid` (`booksellerid`), CONSTRAINT booksellerid_aqcontacts_fk FOREIGN KEY (booksellerid) REFERENCES aqbooksellers (id) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1; @@ -3020,6 +3031,7 @@ CREATE TABLE `aqcontract` ( `contractdescription` mediumtext, `booksellerid` int(11) not NULL, PRIMARY KEY (`contractnumber`), + KEY `booksellerid` (`booksellerid`), CONSTRAINT `booksellerid_fk1` FOREIGN KEY (`booksellerid`) REFERENCES `aqbooksellers` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ; @@ -3070,6 +3082,10 @@ CREATE TABLE `aqorders` ( -- information related to the basket line items KEY `basketno` (`basketno`), KEY `biblionumber` (`biblionumber`), KEY `budget_id` (`budget_id`), + KEY `invoiceid` (`invoiceid`), + KEY `subscriptionid` (`subscriptionid`), + 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, @@ -3135,6 +3151,8 @@ CREATE TABLE aqinvoices ( shipmentcost decimal(28,6) default NULL, -- shipment cost shipmentcost_budgetid int(11) default NULL, -- foreign key to aqbudgets, link the shipment cost to a budget PRIMARY KEY (invoiceid), + KEY `booksellerid` (`booksellerid`), + KEY `shipmentcost_budgetid` (`shipmentcost_budgetid`), CONSTRAINT aqinvoices_fk_aqbooksellerid FOREIGN KEY (booksellerid) REFERENCES aqbooksellers (id) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT aqinvoices_fk_shipmentcost_budgetid FOREIGN KEY (shipmentcost_budgetid) REFERENCES aqbudgets (budget_id) ON DELETE SET NULL ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; -- 2.1.0