---- 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
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.
Created attachment 23024 [details] [review] Bug 3445 - action_logs table needs indexes [2] patch adding indexes to action_logs
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.
It needed a little rebase in updatedatabase, but it works. I sign off. Mathieu
Created attachment 23222 [details] [review] [PATCH]SIGNED OFF Bug 3445 - action_logs table needs indexes
Not sure if that's too many indexes, but it works as expected :)
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.
Pushed to master. Thanks, Fridolin!
Pushed to 3.14.x, will be in 3.14.07