Attachment 63861 Details for Bug 18705 – Bug 18705 - collections_tracking-table is missing index/foreign key to items

[patch] Bug 18705 - collections_tracking-table is missing index/foreign key to items

Bug-18705---collectionstracking-table-is-missing-i.patch (text/plain), 1.92 KB, created by Olli-Antti Kivilahti on 2017-05-31 09:35:28 UTC

(hide)

Description:

Filename:

MIME Type:

Creator: Olli-Antti Kivilahti

Created: 2017-05-31 09:35:28 UTC

Size: 1.92 KB

patch

obsolete

>From 1c6f07b0fbff7952b74c6f31e152f34da43252b5 Mon Sep 17 00:00:00 2001
>From: Olli-Antti Kivilahti <olli-antti.kivilahti@jns.fi>
>Date: Wed, 31 May 2017 12:08:05 +0300
>Subject: [PATCH] Bug 18705 - collections_tracking-table is missing
> index/foreign key to items
>
>This makes searches such as circ/pendingreserves.pl
>rather slow and very noticeable on HDDs
>---
> .../mysql/atomicupdate/Bug-18705-collections_tracking-fk-items.sql    | 3 +++
> installer/data/mysql/kohastructure.sql                                | 4 +++-
> 2 files changed, 6 insertions(+), 1 deletion(-)
> create mode 100644 installer/data/mysql/atomicupdate/Bug-18705-collections_tracking-fk-items.sql
>
>diff --git a/installer/data/mysql/atomicupdate/Bug-18705-collections_tracking-fk-items.sql b/installer/data/mysql/atomicupdate/Bug-18705-collections_tracking-fk-items.sql
>new file mode 100644
>index 0000000..2587a7f
>--- /dev/null
>+++ b/installer/data/mysql/atomicupdate/Bug-18705-collections_tracking-fk-items.sql
>@@ -0,0 +1,3 @@
>+DELETE FROM collections_tracking WHERE itemnumber NOT IN (SELECT itemnumber FROM items WHERE items.itemnumber = collections_tracking.itemnumber);
>+ALTER TABLE collections_tracking ADD CONSTRAINT FOREIGN KEY `coltra-fk-items` (itemnumber) REFERENCES items (itemnumber);
>+
>diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql
>index dd046cc..76d514c 100644
>--- a/installer/data/mysql/kohastructure.sql
>+++ b/installer/data/mysql/kohastructure.sql
>@@ -448,7 +448,9 @@ CREATE TABLE collections_tracking (
>   transferred tinyint(1) DEFAULT '0',
>   date_added datetime DEFAULT NULL,
>   timestamp timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
>-  PRIMARY KEY (collections_tracking_id)
>+  PRIMARY KEY (collections_tracking_id),
>+  CONSTRAINT `coltra-fk-items` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`)
>+    ON DELETE CASCADE ON UPDATE CASCADE
> ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
> 
> --
>-- 
>2.7.4

Actions: View | Diff | Splinter Review

Attachments on bug 18705: 63859 | 63861 | 171017 | 172579