Bug 3445

Summary: action_logs table needs some thought
Product: Koha Reporter: Galen Charlton <gmcharlt>
Component: DatabaseAssignee: Fridolin Somers <fridolin.somers>
Status: CLOSED FIXED QA Contact:
Severity: enhancement    
Priority: P5 - low CC: abl, colin.campbell, fridolin.somers, katrin.fischer, mathsabypro
Version: Main   
Hardware: PC   
OS: All   
Change sponsored?: --- Patch complexity: Small patch
Documentation contact: Documentation submission:
Text to go in the release notes:
Version(s) released in:
Bug Depends on:    
Bug Blocks: 7923    
Attachments: Bug 3445 - action_logs table needs indexes
[PATCH]SIGNED OFF Bug 3445 - action_logs table needs indexes
[PASSED QA] Bug 3445 - action_logs table needs indexes

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.

Mathieu
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