https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=36569 Bug ID: 36569 Summary: Compact action_logs indicies Change sponsored?: --- Product: Koha Version: master Hardware: All OS: All Status: NEW Severity: enhancement Priority: P5 - low Component: Database Assignee: koha-bugs@lists.koha-community.org Reporter: andreas.jonsson@kreablo.se QA Contact: testopia@bugs.koha-community.org With cataloguing logging enabled on the action_logs table it will become large. The module and action columns in action_logs are MEDIUMTEXT (max ~16 million bytes) with 196 first characters used for the index. Since these columns holds names that are only used programatically we could reduce the size of these columns to make the index more compact. Since module and action is often used together we can instead create a combined index and still have a smaller table.
SELECT module, action, count(*) FROM action_logs GROUP BY module, action; +------------------+----------------+----------+ | module | action | count(*) | +------------------+----------------+----------+ | CATALOGUING | ADD | 543638 | | CATALOGUING | DELETE | 573712 | | CATALOGUING | MODIFY | 6264928 | . . . | SYSTEMPREFERENCE | MODIFY | 554 | +------------------+----------------+----------+ 33 rows in set (6 min 49,652 sec)
-rw-rw---- 1 mysql mysql 27439136768 10 apr 16.45 action_logs.ibd -rw-rw---- 1 mysql mysql 24368906240 10 apr 17.33 action_logs.ibd (/ (- 27439136768 24368906240) 27439136768.0) (/ (- 27439136768 24914165760) 27439136768.0) Changing the column sizes to 30 bytes each reduce the size of the action_logs table by 11%.
ALTER TABLE action_logs MODIFY COLUMN module VARCHAR(30) CHARSET ascii, MODIFY COLUMN action VARCHAR(30) CHARSET ascii; Query OK, 8725398 rows affected (18 min 32,541 sec) Records: 8725398 Duplicates: 0 Warnings: 0
After creating a combined index the size is still 9% less than before: CREATE INDEX IF NOT EXISTS module_action_idx ON action_logs(module, action); Performance improvement 22024%: +------------------+----------------+----------+ | module | action | count(*) | +------------------+----------------+----------+ | CATALOGUING | ADD | 543638 | | CATALOGUING | DELETE | 573712 | | CATALOGUING | MODIFY | 6264928 | . . . | SYSTEMPREFERENCE | MODIFY | 554 | +------------------+----------------+----------+ 33 rows in set (1,860 sec) -- You are receiving this mail because: You are watching all bug changes. You are the assignee for the bug.