From c5a748032e0b78965a68199c7a1be1331770475d 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 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 : - 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 | 12 +++++++++++- 1 file changed, 11 insertions(+), 1 deletion(-) diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index eb73cc5..2a9c643 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -2848,6 +2848,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, @@ -2902,6 +2903,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 +2929,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 +2987,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; @@ -3070,6 +3078,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, -- 2.1.0