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.jons...@kreablo.se
QA Contact: testo...@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.
_______________________________________________
Koha-bugs mailing list
Koha-bugs@lists.koha-community.org
https://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-bugs
website : http://www.koha-community.org/
git : http://git.koha-community.org/
bugs : http://bugs.koha-community.org/
