[Bug 7886] New: C4/ShelfBrowser slow SQL performance
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=7886 Priority: P5 - low Change sponsored?: --- Bug ID: 7886 Assignee: oleonard@myacpl.org Summary: C4/ShelfBrowser slow SQL performance QA Contact: koha.sekjal@gmail.com Severity: enhancement Classification: Unclassified OS: All Reporter: dpavlin@rot13.org Hardware: All Status: NEW Version: rel_3_6 Component: OPAC Product: Koha While profiling Koha while writing http://wiki.koha-community.org/wiki/Performance I noticed that ShelfBrowser uses two SQL queries: SELECT * FROM items WHERE ((cn_sort = ? AND itemnumber < ?) OR cn_sort < ?) AND homebranch = ? AND location = ? AND ccode = ? ORDER BY cn_sort DESC, itemnumber LIMIT ? SELECT * FROM items WHERE ((cn_sort = ? AND itemnumber >= ?) OR cn_sort ?) AND homebranch = ? AND location = ? AND ccode = ? ORDER BY cn_sort, itemnumber LIMIT ? Each of this queries takes around 1.5 second on our catalogue with ~340000 items (that's 3 seconds of total query time). homebranch already has index, and adding following indexes: create index items_location on items(location) ; create index items_ccode on items(ccode) ; improves performance by 0.5 seconds (total of 1 second for both queries) since MySQL is able to use index_merge intersect(items_ccode,homebranch,items_location) Since indexes use additional disk space, I'm not sure if this change is applicable to all Koha installations, but I'm looking for feedback. Does it make sense to submit patch with schema change? Ideal solution would be to run those queries once, but for 50 or 100 results, cache results and browse through cache. This would involve one-time penalty hit for first query, but following browsing would be much faster. -- You are receiving this mail because: You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=7886 Dobrica Pavlinusic <dpavlin@rot13.org> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|NEW |In Discussion Blocks| |7172 Assignee|oleonard@myacpl.org |dpavlin@rot13.org -- You are receiving this mail because: You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=7886 Dobrica Pavlinusic <dpavlin@rot13.org> changed: What |Removed |Added ---------------------------------------------------------------------------- Blocks| |7119 -- You are receiving this mail because: You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=7886 Owen Leonard <oleonard@myacpl.org> changed: What |Removed |Added ---------------------------------------------------------------------------- Component|OPAC |Virtual Shelves Version|rel_3_6 |master -- You are receiving this mail because: You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=7886 Paul Poulain <paul.poulain@biblibre.com> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |paul.poulain@biblibre.com --- Comment #1 from Paul Poulain <paul.poulain@biblibre.com> --- Hi Dobrika, 2 points: * most of our (BibLibre) customers already have those indexes, for the reason you point. I thought it was in koha-community in fact... * hardware is so cheap that I don't think the size is really a problem (+ location not a long field, the index must not be that large) So, let's go for it, I remove the "in discussion" status, I don't think it really need a discussion ! -- You are receiving this mail because: You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=7886 Paul Poulain <paul.poulain@biblibre.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|In Discussion |ASSIGNED Severity|enhancement |normal --- Comment #2 from Paul Poulain <paul.poulain@biblibre.com> --- I even think its more a bugfix than an ENH, so also upgrading severity ! -- You are receiving this mail because: You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=7886 --- Comment #3 from Ian Walls <koha.sekjal@gmail.com> --- I support adding these indexes. These codes are often-queried, and we'd get performance gains across the system for the cost of some disk space. I call that win -- You are receiving this mail because: You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=7886 --- Comment #4 from Dobrica Pavlinusic <dpavlin@rot13.org> --- Created attachment 8895 --> http://bugs.koha-community.org/bugzilla3/attachment.cgi?id=8895&action=edit Bug 7886 - C4/ShelfBrowser slow SQL performance ShelfBrowser uses two SQL queries with syntax WHERE ((cn_sort = ? AND itemnumber < ?) OR cn_sort < ?) AND homebranch = ? AND location = ? AND ccode = ? homebranch already has index, and adding indexes on ccode and location improves performance by 30% for each query since MySQL is able to use index_merge intersect(items_ccode,homebranch,items_location) -- You are receiving this mail because: You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=7886 Dobrica Pavlinusic <dpavlin@rot13.org> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|ASSIGNED |Needs Signoff -- You are receiving this mail because: You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=7886 --- Comment #5 from Paul Poulain <paul.poulain@biblibre.com> --- QA comment = dobrica, this patch is correct, but lack kohastructure.sql -- You are receiving this mail because: You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=7886 Dobrica Pavlinusic <dpavlin@rot13.org> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Needs Signoff |Failed QA --- Comment #6 from Dobrica Pavlinusic <dpavlin@rot13.org> --- Sorry, this is my first database patch, I will fix that. -- You are receiving this mail because: You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=7886 Dobrica Pavlinusic <dpavlin@rot13.org> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #8895|0 |1 is obsolete| | --- Comment #7 from Dobrica Pavlinusic <dpavlin@rot13.org> --- Created attachment 8976 --> http://bugs.koha-community.org/bugzilla3/attachment.cgi?id=8976&action=edit Bug 7886 - C4/ShelfBrowser slow SQL performance ShelfBrowser uses two SQL queries with syntax WHERE ((cn_sort = ? AND itemnumber < ?) OR cn_sort < ?) AND homebranch = ? AND location = ? AND ccode = ? homebranch already has index, and adding indexes on ccode and location improves performance by 30% for each query since MySQL is able to use index_merge intersect(items_ccode,homebranch,items_location) -- You are receiving this mail because: You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=7886 Dobrica Pavlinusic <dpavlin@rot13.org> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Failed QA |Needs Signoff -- You are receiving this mail because: You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=7886 Ian Walls <koha.sekjal@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Needs Signoff |Signed Off --- Comment #8 from Ian Walls <koha.sekjal@gmail.com> --- Signing off and Passing QA in one go, as this patch just adds indexes to essential fields, both for new and updating installs. -- You are receiving this mail because: You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=7886 Ian Walls <koha.sekjal@gmail.com> 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=7886 Paul Poulain <paul.poulain@biblibre.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Passed QA |Pushed to Master Version|master |rel_3_6 -- You are receiving this mail because: You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=7886 Jared Camins-Esakov <jcamins@cpbibliography.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Pushed to Master |Pushed to Stable --- Comment #9 from Jared Camins-Esakov <jcamins@cpbibliography.com> --- This patch will appear in 3.6.5. -- You are receiving this mail because: You are watching all bug changes.
participants (1)
-
bugzilla-daemon＠bugs.koha-community.org