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.