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.
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 !
I even think its more a bugfix than an ENH, so also upgrading severity !
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
Created attachment 8895 [details] [review] 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)
QA comment = dobrica, this patch is correct, but lack kohastructure.sql
Sorry, this is my first database patch, I will fix that.
Created attachment 8976 [details] [review] 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)
Signing off and Passing QA in one go, as this patch just adds indexes to essential fields, both for new and updating installs.
This patch will appear in 3.6.5.