http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=7886
Priority: P5 - low
Change sponsored?: ---
Bug ID: 7886
Assignee: oleon...@myacpl.org
Summary: C4/ShelfBrowser slow SQL performance
QA Contact: koha.sek...@gmail.com
Severity: enhancement
Classification: Unclassified
OS: All
Reporter: dpav...@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.
_______________________________________________
Koha-bugs mailing list
Koha-bugs@lists.koha-community.org
http://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-bugs
website : http://www.koha-community.org/
git : http://git.koha-community.org/
bugs : http://bugs.koha-community.org/
