From 0f1562c429e7d05091011ae241871eb4e6a4c333 Mon Sep 17 00:00:00 2001 From: Fridolyn SOMERS Date: Tue, 19 Nov 2013 11:21:13 +0100 Subject: [PATCH] Bug 3445: add more indexes on the action_logs table This patch adds database indexes for action_logs table to speed up the "log viewer" page. Removes the existing index on timestamp+user to add an index on each column since search colums are 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 Signed-off-by: Katrin Fischer Rephrased the updatedatabase message a bit: Add indexes to action_logs table Passes all tests and QA script. Signed-off-by: Galen Charlton (cherry picked from commit 6f728fb05062fc549a635a82970f7dc10f0f9748) Signed-off-by: Fridolin Somers --- 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 5b55b131aa..6e5a95d427 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -2737,7 +2737,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 d98cb0e24c..14dddb66ca 100755 --- a/installer/data/mysql/updatedatabase.pl +++ b/installer/data/mysql/updatedatabase.pl @@ -7953,6 +7953,22 @@ if ( C4::Context->preference("Version") < TransformToNum($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: Add indexes to action_logs table)\n"; + SetVersion($DBversion); +} + =head1 FUNCTIONS =head2 TableExists($table) -- 2.39.5