From fd51ff9612aa5e3d3351546a15c78218fe8a7009 Mon Sep 17 00:00:00 2001 From: Pasi Kallinen Date: Thu, 30 Aug 2018 11:29:54 +0300 Subject: [PATCH] Bug 21288: Slowness in acquisition caused by GetInvoices The SQL query done by GetInvoices is quite slow on our production server, taking about a minute. In the query, there's this join: LEFT JOIN subscription ON biblio.biblionumber = subscription.biblionumber but there's no index on subscription.biblionumber. Adding the index speeds up the query so it only takes up about 3 seconds. Signed-off-by: Pasi Kallinen Signed-off-by: Mark Tompsett Signed-off-by: Chris Cormack --- installer/data/mysql/atomicupdate/bug_21288.perl | 9 +++++++++ installer/data/mysql/kohastructure.sql | 1 + 2 files changed, 10 insertions(+) create mode 100644 installer/data/mysql/atomicupdate/bug_21288.perl diff --git a/installer/data/mysql/atomicupdate/bug_21288.perl b/installer/data/mysql/atomicupdate/bug_21288.perl new file mode 100644 index 0000000000..a4a102a64e --- /dev/null +++ b/installer/data/mysql/atomicupdate/bug_21288.perl @@ -0,0 +1,9 @@ +$DBversion = 'XXX'; # will be replaced by the RM +if( CheckVersion( $DBversion ) ) { + $dbh->do(q{ + CREATE INDEX `by_biblionumber` ON `subscription` (`biblionumber`) + }); + + SetVersion( $DBversion ); + print "Upgrade to $DBversion done (Bug 21288: Slowness in acquisition caused by GetInvoices\n"; +} diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index 3048ade972..da6d7f644e 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -2098,6 +2098,7 @@ CREATE TABLE `subscription` ( -- information related to the subscription `itemtype` VARCHAR( 10 ) NULL, `previousitemtype` VARCHAR( 10 ) NULL, PRIMARY KEY (`subscriptionid`), + KEY `by_biblionumber` (`biblionumber`), CONSTRAINT subscription_ibfk_1 FOREIGN KEY (periodicity) REFERENCES subscription_frequencies (id) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT subscription_ibfk_2 FOREIGN KEY (numberpattern) REFERENCES subscription_numberpatterns (id) ON DELETE SET NULL ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- 2.11.0