[Bug 14053] New: Acquisition db tables are missing indexes
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=14053 Bug ID: 14053 Summary: Acquisition db tables are missing indexes Change sponsored?: --- Product: Koha Version: master Hardware: All OS: All Status: NEW Severity: normal Priority: P5 - low Component: Database Assignee: gmcharlt@gmail.com Reporter: fridolyn.somers@biblibre.com QA Contact: testopia@bugs.koha-community.org Acquisition db tables have a lot of relational constraints and are often missing an index to have performance queries. -- You are receiving this mail because: You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=14053 Fridolin SOMERS <fridolyn.somers@biblibre.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Assignee|gmcharlt@gmail.com |fridolyn.somers@biblibre.co | |m -- You are receiving this mail because: You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=14053 Fridolin SOMERS <fridolyn.somers@biblibre.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|NEW |ASSIGNED -- You are receiving this mail because: You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=14053 Fridolin SOMERS <fridolyn.somers@biblibre.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Blocks| |7923 -- You are receiving this mail because: You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=14053 Fridolin SOMERS <fridolyn.somers@biblibre.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|ASSIGNED |Needs Signoff Patch complexity|--- |Trivial patch -- You are receiving this mail because: You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=14053 --- Comment #1 from Fridolin SOMERS <fridolyn.somers@biblibre.com> --- Created attachment 38568 --> http://bugs.koha-community.org/bugzilla3/attachment.cgi?id=38568&action=edit Bug 14053 - Acquisition db tables are missing indexes Acquisition db tables have a lot of relational constraints and are often missing an index to have performance queries. This patch adds an index for all columns used in a forein key. Also adds an index on some columns very often used in search queries : aqbooksellers.name and aqbudgets.budget_code. Also adds an index on aqorders.orderstatus, very often used with hardcoded value like 'cancelled', in various queries. Test plan : - Check SQL syntax is correct - Try to compare performance after and before database update. I think query contained in C4::Acquistion::GetInvoices could be a good example -- You are receiving this mail because: You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=14053 --- Comment #2 from Fridolin SOMERS <fridolyn.somers@biblibre.com> --- Created attachment 38569 --> http://bugs.koha-community.org/bugzilla3/attachment.cgi?id=38569&action=edit Bug 14053 - Acquisition db tables are missing indexes - atomicupdates -- You are receiving this mail because: You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=14053 --- Comment #3 from M. Tompsett <mtompset@hotmail.com> --- Comment on attachment 38568 --> http://bugs.koha-community.org/bugzilla3/attachment.cgi?id=38568 Bug 14053 - Acquisition db tables are missing indexes Review of attachment 38568: --> (http://bugs.koha-community.org/bugzilla3/page.cgi?id=splinter.html&bug=14053&attachment=38568) ----------------------------------------------------------------- ::: installer/data/mysql/kohastructure.sql @@ +2934,5 @@
+ KEY `budget_parent_id` (`budget_parent_id`), + KEY `budget_code` (`budget_code`), + KEY `budget_branchcode` (`budget_branchcode`), + KEY `budget_period_id` (`budget_period_id`), + KEY `budget_owner_id` (`budget_owner_id`),
I believe this trailing comma is an issue. I dropped koha_library. created it. then run web install. -- You are receiving this mail because: You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=14053 M. Tompsett <mtompset@hotmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |mtompset@hotmail.com Status|Needs Signoff |Failed QA -- You are receiving this mail because: You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=14053 --- Comment #4 from M. Tompsett <mtompset@hotmail.com> --- Comment on attachment 38569 --> http://bugs.koha-community.org/bugzilla3/attachment.cgi?id=38569 Bug 14053 - Acquisition db tables are missing indexes - atomicupdates Review of attachment 38569: --> (http://bugs.koha-community.org/bugzilla3/page.cgi?id=splinter.html&bug=14053&attachment=38569) ----------------------------------------------------------------- Atomic updates worked fine. -- You are receiving this mail because: You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=14053 --- Comment #5 from M. Tompsett <mtompset@hotmail.com> --- I checked the SQL syntax. I tweaked the SQL to not fail for me. Current master needs: sudo apt-get install libgraphics-colornames-perl sudo apt-get install libimage-size-perl sudo apt-get install libhtml-tidy-perl cpan2deb PDF::Writer sudo dpkg -i libpdf-writer-perl_0.06-1_all.deb cpan2deb PDF::FromHTML sudo dpkg -i libpdf-fromhtml-perl_0.31-1_all.deb I did not check performance, though I doubt this would hinder it. -- You are receiving this mail because: You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=14053 M. Tompsett <mtompset@hotmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #38568|0 |1 is obsolete| | --- Comment #6 from M. Tompsett <mtompset@hotmail.com> --- Created attachment 38728 --> http://bugs.koha-community.org/bugzilla3/attachment.cgi?id=38728&action=edit Bug 14053 - Acquisition db tables are missing indexes Acquisition db tables have a lot of relational constraints and are often missing an index to have performance queries. This patch adds an index for all columns used in a forein key. Also adds an index on some columns very often used in search queries : aqbooksellers.name and aqbudgets.budget_code. Also adds an index on aqorders.orderstatus, very often used with hardcoded value like 'cancelled', in various queries. Test plan : - Check SQL syntax is correct - Try to compare performance after and before database update. I think query contained in C4::Acquistion::GetInvoices could be a good example Signed-off-by: Mark Tompsett <mtompset@hotmail.com> -- You are receiving this mail because: You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=14053 M. Tompsett <mtompset@hotmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #38569|0 |1 is obsolete| | --- Comment #7 from M. Tompsett <mtompset@hotmail.com> --- Created attachment 38729 --> http://bugs.koha-community.org/bugzilla3/attachment.cgi?id=38729&action=edit Bug 14053 - Acquisition db tables are missing indexes - atomicupdates Signed-off-by: Mark Tompsett <mtompset@hotmail.com> -- You are receiving this mail because: You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=14053 M. Tompsett <mtompset@hotmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Failed QA |Needs Signoff --- Comment #8 from M. Tompsett <mtompset@hotmail.com> --- Fridolin, could you please confirm my tweaks? See comment #5 about issues you may encounter. If it works for you, then feel free to set it to signed off. -- You are receiving this mail because: You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=14053 --- Comment #9 from Marc Véron <veron@veron.ch> --- Created attachment 38808 --> http://bugs.koha-community.org/bugzilla3/attachment.cgi?id=38808&action=edit [Signed-off] Bug 14053 - Acquisition db tables are missing indexes - atomicupdates Signed-off-by: Mark Tompsett <mtompset@hotmail.com> Atomic updates worked OK Signed-off-by: Marc Véron <veron@veron.ch> -- You are receiving this mail because: You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=14053 Marc Véron <veron@veron.ch> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |veron@veron.ch Attachment #38729|0 |1 is obsolete| | -- You are receiving this mail because: You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=14053 Marc Véron <veron@veron.ch> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Needs Signoff |Signed Off -- You are receiving this mail because: You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=14053 Fridolin SOMERS <fridolyn.somers@biblibre.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Signed Off |ASSIGNED --- Comment #10 from Fridolin SOMERS <fridolyn.somers@biblibre.com> --- I have to review those patches because I discovered that adding a foreign key constraint automatically adds an index -- You are receiving this mail because: You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=14053 Fridolin SOMERS <fridolyn.somers@biblibre.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #38728|0 |1 is obsolete| | -- You are receiving this mail because: You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=14053 Fridolin SOMERS <fridolyn.somers@biblibre.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #38808|0 |1 is obsolete| | -- You are receiving this mail because: You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=14053 Fridolin SOMERS <fridolyn.somers@biblibre.com> changed: What |Removed |Added ---------------------------------------------------------------------------- See Also| |http://bugs.koha-community. | |org/bugzilla3/show_bug.cgi? | |id=14132 -- You are receiving this mail because: You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=14053 --- Comment #11 from Fridolin SOMERS <fridolyn.somers@biblibre.com> --- Created attachment 39135 --> http://bugs.koha-community.org/bugzilla3/attachment.cgi?id=39135&action=edit Bug 14053 - Acquisition db tables are missing indexes Acquisition db tables are missing some indexes to have performance queries. This patch adds an index on some columns very often used in search queries, such as aqbooksellers.name and aqbudgets.budget_code. Also adds an index on aqorders.orderstatus, very often used with hardcoded value like 'cancelled', in various queries. Test plan : - Check SQL syntax is correct - Try to compare performance after and before database update. I think query contained in C4::Acquistion::GetInvoices could be a good example -- You are receiving this mail because: You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=14053 --- Comment #12 from Fridolin SOMERS <fridolyn.somers@biblibre.com> --- Created attachment 39136 --> http://bugs.koha-community.org/bugzilla3/attachment.cgi?id=39136&action=edit Bug 14053 - Acquisition db tables are missing indexes - atomicupdates -- You are receiving this mail because: You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=14053 Fridolin SOMERS <fridolyn.somers@biblibre.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|ASSIGNED |Needs Signoff --- Comment #13 from Fridolin SOMERS <fridolyn.somers@biblibre.com> --- Patches updated -- You are receiving this mail because: You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=14053 --- Comment #14 from M. Tompsett <mtompset@hotmail.com> --- Comment on attachment 39135 --> http://bugs.koha-community.org/bugzilla3/attachment.cgi?id=39135 Bug 14053 - Acquisition db tables are missing indexes Review of attachment 39135: --> (http://bugs.koha-community.org/bugzilla3/page.cgi?id=splinter.html&bug=14053&attachment=39135) ----------------------------------------------------------------- ::: installer/data/mysql/kohastructure.sql @@ +2903,4 @@
PRIMARY KEY (`id`), KEY `listprice` (`listprice`), KEY `invoiceprice` (`invoiceprice`), + KEY `name` (`name`(255)),
Remove (255) This causes a failure. -- You are receiving this mail because: You are watching all bug changes.
DROP DATABASE koha_library; CREATE DATABASE koha_library; QUIT;
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=14053 --- Comment #15 from M. Tompsett <mtompset@hotmail.com> --- Created attachment 39153 --> http://bugs.koha-community.org/bugzilla3/attachment.cgi?id=39153&action=edit Bug 14053 - Fix cut and paste mistake (255) does not belong in the key definition. TEST PLAN ---------- 1) Back up db. 2) $ git reset --hard origin/master 3) $ git bz apply 14053 -- do not apply this patch. 4) in your mysql client: 5) Navigate to staff client -- blows up in step 3 6) $ git bz apply 14053 -- apply just this final patch 7) repeat step 4. 8) Navigate to staff client -- everything proceeds smoothly. 9) run koha qa test tools -- You are receiving this mail because: You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=14053 M. Tompsett <mtompset@hotmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #39153|0 |1 is obsolete| | --- Comment #16 from M. Tompsett <mtompset@hotmail.com> --- Comment on attachment 39153 --> http://bugs.koha-community.org/bugzilla3/attachment.cgi?id=39153 Bug 14053 - Fix cut and paste mistake Nope that's not the solution. Sorry, my bad. -- You are receiving this mail because: You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=14053 --- Comment #17 from M. Tompsett <mtompset@hotmail.com> --- Comment on attachment 39135 --> http://bugs.koha-community.org/bugzilla3/attachment.cgi?id=39135 Bug 14053 - Acquisition db tables are missing indexes Review of attachment 39135: --> (http://bugs.koha-community.org/bugzilla3/page.cgi?id=splinter.html&bug=14053&attachment=39135) ----------------------------------------------------------------- ::: installer/data/mysql/kohastructure.sql @@ +2903,4 @@
PRIMARY KEY (`id`), KEY `listprice` (`listprice`), KEY `invoiceprice` (`invoiceprice`), + KEY `name` (`name`(255)),
This wasn't the problem. @@ +2933,5 @@
+ KEY `budget_parent_id` (`budget_parent_id`), + KEY `budget_code` (`budget_code`), + KEY `budget_branchcode` (`budget_branchcode`), + KEY `budget_period_id` (`budget_period_id`), + KEY `budget_owner_id` (`budget_owner_id`),
This comma is the problem! I'm going to remove it from your patch and sign off. -- You are receiving this mail because: You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=14053 --- Comment #18 from M. Tompsett <mtompset@hotmail.com> --- (In reply to M. Tompsett from comment #3)
Comment on attachment 38568 [details] [review] Bug 14053 - Acquisition db tables are missing indexes
Review of attachment 38568 [details] [review]: -----------------------------------------------------------------
::: installer/data/mysql/kohastructure.sql @@ +2934,5 @@
+ KEY `budget_parent_id` (`budget_parent_id`), + KEY `budget_code` (`budget_code`), + KEY `budget_branchcode` (`budget_branchcode`), + KEY `budget_period_id` (`budget_period_id`), + KEY `budget_owner_id` (`budget_owner_id`),
I believe this trailing comma is an issue. I dropped koha_library. created it. then run web install.
Please fix your branch. This comma bug was introduced again. -- You are receiving this mail because: You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=14053 M. Tompsett <mtompset@hotmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #39136|0 |1 is obsolete| | --- Comment #19 from M. Tompsett <mtompset@hotmail.com> --- Created attachment 39154 --> http://bugs.koha-community.org/bugzilla3/attachment.cgi?id=39154&action=edit Bug 14053 - Acquisition db tables are missing indexes - atomicupdates Signed-off-by: Mark Tompsett <mtompset@hotmail.com> -- You are receiving this mail because: You are watching all bug changes.
DROP DATABASE koha_library; CREATE DATABASE koha_library; QUIT; -- Obviously you may need to vary koha_library :)
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=14053 M. Tompsett <mtompset@hotmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #39135|0 |1 is obsolete| | --- Comment #20 from M. Tompsett <mtompset@hotmail.com> --- Created attachment 39155 --> http://bugs.koha-community.org/bugzilla3/attachment.cgi?id=39155&action=edit Bug 14053 - Acquisition db tables are missing indexes Acquisition db tables are missing some indexes to have performance queries. This patch adds an index on some columns very often used in search queries, such as aqbooksellers.name and aqbudgets.budget_code. Also adds an index on aqorders.orderstatus, very often used with hardcoded value like 'cancelled', in various queries. Test plan : 1) Back up database 2) $ git reset --hard origin/master 3) $ git bz apply 14053 4) In your mysql client 5) Navigate to staff client -- should be able to set up the DB just fine. -- this will catch the comma bug that keeps coming in. 6) $ git reset --hard origin/master 7) Repeat step 4 8) Navigate to staff client -- nothing tested, but we need the DB set up. 9) $ ./installer/data/mysql/updatedatabase.pl -- atomic updates run without issue. 10) run koha qa test tools 11) Restore DB 12) Try to compare performance after and before database update. I think query contained in C4::Acquistion::GetInvoices could be a good example Signed-off-by: Mark Tompsett <mtompset@hotmail.com> -- You are receiving this mail because: You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=14053 M. Tompsett <mtompset@hotmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Needs Signoff |Signed Off --- Comment #21 from M. Tompsett <mtompset@hotmail.com> --- NOTE: I did not compare performance issues, though it conceptually makes sense. My sign offs are based on tweaked (I fixed the comma bug) patches and a more explicit test plan (to prevent the comma bug from returning). -- You are receiving this mail because: You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=14053 Jonathan Druart <jonathan.druart@biblibre.com> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |jonathan.druart@biblibre.co | |m --- Comment #22 from Jonathan Druart <jonathan.druart@biblibre.com> --- (In reply to Fridolin SOMERS from comment #1)
- Try to compare performance after and before database update. I think query contained in C4::Acquistion::GetInvoices could be a good example
I only have ~20 invoices in my DB, not sure it will be enough. Don't you have number you would have measured? In production for instance? -- You are receiving this mail because: You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=14053 Katrin Fischer <katrin.fischer@bsz-bw.de> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Signed Off |Passed QA -- You are receiving this mail because: You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=14053 Katrin Fischer <katrin.fischer@bsz-bw.de> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #39154|0 |1 is obsolete| | Attachment #39155|0 |1 is obsolete| | --- Comment #23 from Katrin Fischer <katrin.fischer@bsz-bw.de> --- Created attachment 39719 --> http://bugs.koha-community.org/bugzilla3/attachment.cgi?id=39719&action=edit [PASSED QA] Bug 14053 - Acquisition db tables are missing indexes - atomicupdates Signed-off-by: Mark Tompsett <mtompset@hotmail.com> Signed-off-by: Katrin Fischer <katrin.fischer.83@web.de> -- You are receiving this mail because: You are watching all bug changes.
DROP DATABASE koha_library; CREATE DATABASE koha_library; QUIT; -- Obviously you may need to vary koha_library :)
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=14053 --- Comment #24 from Katrin Fischer <katrin.fischer@bsz-bw.de> --- Created attachment 39720 --> http://bugs.koha-community.org/bugzilla3/attachment.cgi?id=39720&action=edit [PASSED QA] Bug 14053 - Acquisition db tables are missing indexes Acquisition db tables are missing some indexes to have performance queries. This patch adds an index on some columns very often used in search queries, such as aqbooksellers.name and aqbudgets.budget_code. Also adds an index on aqorders.orderstatus, very often used with hardcoded value like 'cancelled', in various queries. Test plan : 1) Back up database 2) $ git reset --hard origin/master 3) $ git bz apply 14053 4) In your mysql client 5) Navigate to staff client -- should be able to set up the DB just fine. -- this will catch the comma bug that keeps coming in. 6) $ git reset --hard origin/master 7) Repeat step 4 8) Navigate to staff client -- nothing tested, but we need the DB set up. 9) $ ./installer/data/mysql/updatedatabase.pl -- atomic updates run without issue. 10) run koha qa test tools 11) Restore DB 12) Try to compare performance after and before database update. I think query contained in C4::Acquistion::GetInvoices could be a good example Signed-off-by: Mark Tompsett <mtompset@hotmail.com> Signed-off-by: Katrin Fischer <katrin.fischer.83@web.de> -- You are receiving this mail because: You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=14053 Tomás Cohen Arazi <tomascohen@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |tomascohen@gmail.com Status|Passed QA |Pushed to Master --- Comment #25 from Tomás Cohen Arazi <tomascohen@gmail.com> --- Patch pushed to master. Thanks Frido! -- You are receiving this mail because: You are watching all bug changes.
participants (1)
-
bugzilla-daemon＠bugs.koha-community.org