From 92b82bcf18dcbef342fa7db82aad5542f62d83e9 Mon Sep 17 00:00:00 2001
From: Fridolyn SOMERS <fridolyn.somers@biblibre.com>
Date: Tue, 19 Nov 2013 11:21:13 +0100
Subject: [PATCH][SIGNED OFF] Bug 3445 - action_logs table needs indexes
Content-Type: text/plain; charset="utf-8"
This patch adds database indexes for action_logs table to speed up "log viewer" page.
Removes the existing index on timestamp+user to add an indexe on each column since earch colums is separately defined in log viewer form.
Test plan:
- Update database
- Play with log viewer : /cgi-bin/koha/tools/viewlog.pl
- Perform searches with only one filter defined
- Also check you see indexes with SQL query : SHOW CREATE TABLE action_logs
Signed-off-by: Mathieu Saby <mathieu.saby@univ-rennes2.fr>
---
installer/data/mysql/kohastructure.sql | 7 ++++++-
installer/data/mysql/updatedatabase.pl | 16 ++++++++++++++++
2 files changed, 22 insertions(+), 1 deletion(-)
diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql
index 07160c8..36b8ab3 100644
--- a/installer/data/mysql/kohastructure.sql
+++ b/installer/data/mysql/kohastructure.sql
@@ -2735,7 +2735,12 @@ CREATE TABLE `action_logs` ( -- logs of actions taken in Koha (requires that the
`object` int(11) default NULL, -- the object that the action was taken against (could be a borrowernumber, itemnumber, etc)
`info` text, -- information about the action (usually includes SQL statement)
PRIMARY KEY (`action_id`),
- KEY (`timestamp`,`user`)
+ KEY `timestamp_idx` (`timestamp`),
+ KEY `user_idx` (`user`),
+ KEY `module_idx` (`module`(255)),
+ KEY `action_idx` (`action`(255)),
+ KEY `object_idx` (`object`),
+ KEY `info_idx` (`info`(255))
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
diff --git a/installer/data/mysql/updatedatabase.pl b/installer/data/mysql/updatedatabase.pl
index a9bfbbf..4e178be 100755
--- a/installer/data/mysql/updatedatabase.pl
+++ b/installer/data/mysql/updatedatabase.pl
@@ -7778,6 +7778,22 @@ if(CheckVersion($DBversion)) {
SetVersion($DBversion);
}
+$DBversion = "3.15.00.XXX";
+if(CheckVersion($DBversion)) {
+ $dbh->do(q{
+ ALTER TABLE `action_logs`
+ DROP KEY timestamp,
+ ADD KEY `timestamp_idx` (`timestamp`),
+ ADD KEY `user_idx` (`user`),
+ ADD KEY `module_idx` (`module`(255)),
+ ADD KEY `action_idx` (`action`(255)),
+ ADD KEY `object_idx` (`object`),
+ ADD KEY `info_idx` (`info`(255))
+ });
+ print "Upgrade to $DBversion done (Bug 3445 - action_logs table needs some thought)\n";
+ SetVersion($DBversion);
+}
+
=head1 FUNCTIONS
=head2 TableExists($table)
--
1.7.9.5