[Bug 36033] New: Table pseudonymized_transactions needs more indexes
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=36033 Bug ID: 36033 Summary: Table pseudonymized_transactions needs more indexes Change sponsored?: --- Product: Koha Version: master Hardware: All OS: All Status: NEW Severity: minor Priority: P5 - low Component: Database Assignee: koha-bugs@lists.koha-community.org Reporter: fridolin.somers@biblibre.com QA Contact: testopia@bugs.koha-community.org Table pseudonymized_transactions contains : KEY `pseudonymized_transactions_ibfk_1` (`categorycode`), KEY `pseudonymized_transactions_borrowers_ibfk_2` (`branchcode`), KEY `pseudonymized_transactions_borrowers_ibfk_3` (`transaction_branchcode`) To improve SQL queries performance, it needs more indexes, specially on itemnumber. -- You are receiving this mail because: You are watching all bug changes. You are the assignee for the bug.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=36033 Fridolin Somers <fridolin.somers@biblibre.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Assignee|koha-bugs@lists.koha-commun |fridolin.somers@biblibre.co |ity.org |m Status|NEW |ASSIGNED -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=36033 Fridolin Somers <fridolin.somers@biblibre.com> changed: What |Removed |Added ---------------------------------------------------------------------------- See Also| |https://bugs.koha-community | |.org/bugzilla3/show_bug.cgi | |?id=33890 -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=36033 Fridolin Somers <fridolin.somers@biblibre.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Patch complexity|--- |Trivial patch Status|ASSIGNED |Needs Signoff -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=36033 --- Comment #1 from Fridolin Somers <fridolin.somers@biblibre.com> --- Created attachment 161879 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=161879&action=edit Bug 36033: Add more indexes to table pseudonymized_transactions Table pseudonymized_transactions contains : KEY `pseudonymized_transactions_ibfk_1` (`categorycode`), KEY `pseudonymized_transactions_borrowers_ibfk_2` (`branchcode`), KEY `pseudonymized_transactions_borrowers_ibfk_3` (`transaction_branchcode`) To improve SQL queries performance, it needs more indexes, specially on itemnumber. Looking at table statistics : KEY `timeidx` (`datetime`), KEY `branch_idx` (`branch`), KEY `type_idx` (`type`), KEY `itemnumber_idx` (`itemnumber`), So index is need on pseudonymized_transactions columns : itemnumber => For join with table items transaction_type => For filter on type issue, return ... datetime => For filter on date, this will help cleanup script Test plan : 1) Run updatedatabase.pl 2) Check indexes are created in table pseudonymized_transactions 3) Run SQL query : describe select * from pseudonymized_transactions join items using(itemnumber) where transaction_type='issue' and datetime < date_sub(curdate(), INTERVAL 30 DAY) => You see the 3 new indexes used in 'possible_keys'. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=36033 Fridolin Somers <fridolin.somers@biblibre.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #161879|0 |1 is obsolete| | --- Comment #2 from Fridolin Somers <fridolin.somers@biblibre.com> --- Created attachment 161880 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=161880&action=edit Bug 36033: Add more indexes to table pseudonymized_transactions Table pseudonymized_transactions contains : KEY `pseudonymized_transactions_ibfk_1` (`categorycode`), KEY `pseudonymized_transactions_borrowers_ibfk_2` (`branchcode`), KEY `pseudonymized_transactions_borrowers_ibfk_3` (`transaction_branchcode`) To improve SQL queries performance, it needs more indexes, specially on itemnumber. Looking at table statistics : KEY `timeidx` (`datetime`), KEY `branch_idx` (`branch`), KEY `type_idx` (`type`), KEY `itemnumber_idx` (`itemnumber`), So index is need on pseudonymized_transactions columns : itemnumber => For join with table items transaction_type => For filter on type issue, return ... datetime => For filter on date, this will help cleanup script Test plan : 1) Run updatedatabase.pl 2) Check indexes are created in table pseudonymized_transactions 3) Run SQL query : describe select * from pseudonymized_transactions join items using(itemnumber) where transaction_type='issue' and datetime < date_sub(curdate(), INTERVAL 30 DAY) => You see the 3 new indexes used in 'possible_keys'. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=36033 David Nind <david@davidnind.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Needs Signoff |Signed Off -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=36033 David Nind <david@davidnind.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #161880|0 |1 is obsolete| | --- Comment #3 from David Nind <david@davidnind.com> --- Created attachment 162012 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=162012&action=edit Bug 36033: Add more indexes to table pseudonymized_transactions Table pseudonymized_transactions contains : KEY `pseudonymized_transactions_ibfk_1` (`categorycode`), KEY `pseudonymized_transactions_borrowers_ibfk_2` (`branchcode`), KEY `pseudonymized_transactions_borrowers_ibfk_3` (`transaction_branchcode`) To improve SQL queries performance, it needs more indexes, specially on itemnumber. Looking at table statistics : KEY `timeidx` (`datetime`), KEY `branch_idx` (`branch`), KEY `type_idx` (`type`), KEY `itemnumber_idx` (`itemnumber`), So index is need on pseudonymized_transactions columns : itemnumber => For join with table items transaction_type => For filter on type issue, return ... datetime => For filter on date, this will help cleanup script Test plan : 1) Run updatedatabase.pl 2) Check indexes are created in table pseudonymized_transactions 3) Run SQL query : describe select * from pseudonymized_transactions join items using(itemnumber) where transaction_type='issue' and datetime < date_sub(curdate(), INTERVAL 30 DAY) => You see the 3 new indexes used in 'possible_keys'. Signed-off-by: David Nind <david@davidnind.com> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=36033 David Nind <david@davidnind.com> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |david@davidnind.com --- Comment #4 from David Nind <david@davidnind.com> --- Results of the query before and after the patch is applied ========================================================== Before: +------+-------------+----------------------------+--------+---------------+---------+---------+----------------------------------------------------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+----------------------------+--------+---------------+---------+---------+----------------------------------------------------+------+-------------+ | 1 | SIMPLE | pseudonymized_transactions | ALL | NULL | NULL | NULL | NULL | 1 | Using where | | 1 | SIMPLE | items | eq_ref | PRIMARY | PRIMARY | 4 | koha_kohadev.pseudonymized_transactions.itemnumber | 1 | | +------+-------------+----------------------------+--------+---------------+---------+---------+----------------------------------------------------+------+-------------+ After: +------+-------------+----------------------------+--------+-------------------------------------------------------------------------------------------------------------+-----------------------------------+---------+----------------------------------------------------+------+------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+----------------------------+--------+-------------------------------------------------------------------------------------------------------------+-----------------------------------+---------+----------------------------------------------------+------+------------------------------------+ | 1 | SIMPLE | pseudonymized_transactions | ref | pseudonymized_transactions_items_ibfk_4,pseudonymized_transactions_ibfk_5,pseudonymized_transactions_ibfk_6 | pseudonymized_transactions_ibfk_5 | 67 | const | 1 | Using index condition; Using where | | 1 | SIMPLE | items | eq_ref | PRIMARY | PRIMARY | 4 | koha_kohadev.pseudonymized_transactions.itemnumber | 1 | | +------+-------------+----------------------------+--------+-------------------------------------------------------------------------------------------------------------+-----------------------------------+---------+----------------------------------------------------+------+------------------------------------+ -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=36033 Marcel de Rooy <m.de.rooy@rijksmuseum.nl> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |m.de.rooy@rijksmuseum.nl --- Comment #5 from Marcel de Rooy <m.de.rooy@rijksmuseum.nl> --- (In reply to Fridolin Somers from comment #0)
To improve SQL queries performance, it needs more indexes, specially on itemnumber.
Could you provide further details for the need of those indexes? I am seeing a search on datetime in cleanup_database. Where, when do we need the other ones? KEY `pseudonymized_transactions_ibfk_1` (`categorycode`), KEY `pseudonymized_transactions_borrowers_ibfk_2` (`branchcode`), - KEY `pseudonymized_transactions_borrowers_ibfk_3` (`transaction_branchcode`) + KEY `pseudonymized_transactions_borrowers_ibfk_3` (`transaction_branchcode`), + KEY `pseudonymized_transactions_items_ibfk_4` (`itemnumber`), + KEY `pseudonymized_transactions_ibfk_5` (`transaction_type`), + KEY `pseudonymized_transactions_ibfk_6` (`datetime`) The names are confusing? But seeing more confusion in structure there. No blocker. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=36033 --- Comment #6 from Fridolin Somers <fridolin.somers@biblibre.com> --- Table "pseudonymized_transactions" is dedicated to be used in SQL reports. There is a good chance "transaction_type" will be used to count checkouts, checkins ... The "itemnumber" can be used for a join with items table. This will be much more performant with an index. Some item datas are in pseudonymized_transactions (for when item is deleted). But for catalogs that mostly do not delete there items they may want to join for more datas. Also can be used to count patron checkouts on same item. See that table "statistics" as indexes : KEY `type_idx` (`type`), KEY `itemnumber_idx` (`itemnumber`), I agree we need to balance query performance with row creating speed. So we may not add indexes on all columns. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=36033 Marcel de Rooy <m.de.rooy@rijksmuseum.nl> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Signed Off |Passed QA -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=36033 Marcel de Rooy <m.de.rooy@rijksmuseum.nl> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #162012|0 |1 is obsolete| | --- Comment #7 from Marcel de Rooy <m.de.rooy@rijksmuseum.nl> --- Created attachment 163659 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=163659&action=edit Bug 36033: Add more indexes to table pseudonymized_transactions Table pseudonymized_transactions contains : KEY `pseudonymized_transactions_ibfk_1` (`categorycode`), KEY `pseudonymized_transactions_borrowers_ibfk_2` (`branchcode`), KEY `pseudonymized_transactions_borrowers_ibfk_3` (`transaction_branchcode`) To improve SQL queries performance, it needs more indexes, specially on itemnumber. Looking at table statistics : KEY `timeidx` (`datetime`), KEY `branch_idx` (`branch`), KEY `type_idx` (`type`), KEY `itemnumber_idx` (`itemnumber`), So index is need on pseudonymized_transactions columns : itemnumber => For join with table items transaction_type => For filter on type issue, return ... datetime => For filter on date, this will help cleanup script Test plan : 1) Run updatedatabase.pl 2) Check indexes are created in table pseudonymized_transactions 3) Run SQL query : describe select * from pseudonymized_transactions join items using(itemnumber) where transaction_type='issue' and datetime < date_sub(curdate(), INTERVAL 30 DAY) => You see the 3 new indexes used in 'possible_keys'. Signed-off-by: David Nind <david@davidnind.com> Signed-off-by: Marcel de Rooy <m.de.rooy@rijksmuseum.nl> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=36033 Katrin Fischer <katrin.fischer@bsz-bw.de> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Passed QA |Pushed to master Version(s)| |24.05.00 released in| | -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=36033 --- Comment #8 from Katrin Fischer <katrin.fischer@bsz-bw.de> --- Pushed for 24.05! Well done everyone, thank you! -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=36033 Fridolin Somers <fridolin.somers@biblibre.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Pushed to main |Pushed to stable Version(s)|24.05.00 |24.05.00,23.11.06 released in| | --- Comment #9 from Fridolin Somers <fridolin.somers@biblibre.com> --- Pushed to 23.11.x for 23.11.06 -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=36033 Lucas Gass <lucas@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Version(s)|24.05.00,23.11.06 |24.05.00,23.11.06,23.05.12 released in| | CC| |lucas@bywatersolutions.com Status|Pushed to stable |Pushed to oldstable --- Comment #10 from Lucas Gass <lucas@bywatersolutions.com> --- Backported to 23.05.x for upcoming 23.05.12 -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=36033 --- Comment #11 from Jonathan Druart <jonathan.druart@gmail.com> --- Created attachment 187674 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=187674&action=edit Bug 36033: Fix DB struct diff -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=36033 Lucas Gass (lukeg) <lucas@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #187674|0 |1 is obsolete| | --- Comment #12 from Lucas Gass (lukeg) <lucas@bywatersolutions.com> --- Created attachment 187698 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=187698&action=edit Bug 36033: Fix DB struct diff Signed-off-by: Lucas Gass <lucas@bywatersolutions.com> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=36033 --- Comment #13 from Lucas Gass (lukeg) <lucas@bywatersolutions.com> --- follow-up pushed to main -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=36033 --- Comment #14 from Lucas Gass (lukeg) <lucas@bywatersolutions.com> --- follow-up pushed to main -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=36033 Fridolin Somers <fridolin.somers@biblibre.com> changed: What |Removed |Added ---------------------------------------------------------------------------- See Also| |https://bugs.koha-community | |.org/bugzilla3/show_bug.cgi | |?id=37901 -- You are receiving this mail because: You are watching all bug changes.
participants (1)
-
bugzilla-daemon＠bugs.koha-community.org