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 <pasi.kallinen@joensuu.fi>

Signed-off-by: Mark Tompsett <mtompset@hotmail.com>
Signed-off-by: Chris Cormack <chris@bigballofwax.co.nz>

Signed-off-by: Nick Clemens <nick@bywatersolutions.com>

diff --git a/installer/data/mysql/atomicupdate/bug_21288.perl b/installer/data/mysql/atomicupdate/bug_21288.perl
new file mode 100644 (file)
index 0000000..a4a102a
--- /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 3048ade..da6d7f6 100644 (file)
--- 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;