Bug 3445 - action_logs table needs some thought
Summary: action_logs table needs some thought
Alias: None
Product: Koha
Classification: Unclassified
Component: Database (show other bugs)
Version: master
Hardware: PC All
: P5 - low enhancement (vote)
Assignee: Fridolin Somers
QA Contact:
Depends on:
Blocks: 7923
  Show dependency treegraph
Reported: 2009-07-28 01:33 UTC by Galen Charlton
Modified: 2015-12-03 22:02 UTC (History)
5 users (show)

See Also:
Change sponsored?: ---
Patch complexity: Small patch
Documentation contact:
Documentation submission:
Text to go in the release notes:
Version(s) released in:

Bug 3445 - action_logs table needs indexes (2.54 KB, patch)
2013-11-19 10:49 UTC, Fridolin Somers
Details | Diff | Splinter Review
[PATCH]SIGNED OFF Bug 3445 - action_logs table needs indexes (2.65 KB, patch)
2013-11-29 12:47 UTC, mathieu saby
Details | Diff | Splinter Review
[PASSED QA] Bug 3445 - action_logs table needs indexes (2.76 KB, patch)
2013-12-29 20:42 UTC, Katrin Fischer
Details | Diff | Splinter Review

Note You need to log in before you can comment on or make changes to this bug.
Description Chris Cormack 2010-05-21 01:10:35 UTC

---- Reported by gmcharlt@gmail.com 2009-07-28 01:33:28 ----

More and more code is increasingly depending on queries against the action_logs table, which is not currently well-designed for that purpose.  Some changes to that table should be considered, including:

[1] Defining separate columns for the specific record type of record ID being logged, e.g., borrowernumber, biblionumber, itemnumber, serialid, etc.

[2] Defining additional indexes, include ones on

* user
* module
* action
* module, action
* object (or the new ID columns specified in [1])

---- Additional Comments From joe.atzberger@liblime.com 2009-07-28 01:37:21 ----

Agreed, the action log is *highly* unreliable for all but the most rudimentary purposes.  

A third needed element:
(3) documentation of known points of input, i.e. use cases, so that data is not misinterpreted on its way out as being from one "action", when it is actually a composite of several, with possibly varying robustness in data population.

--- Bug imported by chris@bigballofwax.co.nz 2010-05-21 01:10 UTC  ---

This bug was previously known as _bug_ 3445 at http://bugs.koha.org/cgi-bin/bugzilla3/show_bug.cgi?id=3445

Actual time not defined. Setting to 0.0
CC member joe.atzberger@liblime.com does not have an account here

Comment 1 Katrin Fischer 2013-04-13 17:24:57 UTC
The table structure doesn't show columns for borrowernumber, biblionumber etc. but this might be quite hard to implement right now. Also no indexes for user, module, action, etc.
Comment 2 Fridolin Somers 2013-11-19 10:49:32 UTC Comment hidden (obsolete)
Comment 3 Fridolin Somers 2013-11-19 10:54:04 UTC
This is important because without db indexes, using log viewer may block the database for a moment.
In some cases, action_logs table has more than a million rows.

Also this may be a bug more than an enhancement.
Comment 4 mathieu saby 2013-11-29 12:45:12 UTC
It needed a little rebase in updatedatabase, but it works. I sign off.

Comment 5 mathieu saby 2013-11-29 12:47:07 UTC Comment hidden (obsolete)
Comment 6 Katrin Fischer 2013-12-29 20:42:23 UTC
Not sure if that's too many indexes, but it works as expected :)
Comment 7 Katrin Fischer 2013-12-29 20:42:54 UTC
Created attachment 23866 [details] [review]
[PASSED QA] Bug 3445 - action_logs table needs indexes

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>

Signed-off-by: Katrin Fischer <Katrin.Fischer.83@web.de>
Rephrased the updatedatabase message a bit:
Add indexes to action_logs table
Passes all tests and QA script.
Comment 8 Galen Charlton 2014-03-10 21:16:24 UTC
Pushed to master.  Thanks, Fridolin!
Comment 9 Fridolin Somers 2014-05-27 14:39:17 UTC
Pushed to 3.14.x, will be in 3.14.07