[Koha-bugs] [Bug 7886] New: C4/ShelfBrowser slow SQL performance

[bugzilla-daemon at bugs.koha-community.org]

http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=7886

          Priority: P5 - low
 Change sponsored?: ---
            Bug ID: 7886
          Assignee: oleonard at myacpl.org
           Summary: C4/ShelfBrowser slow SQL performance
        QA Contact: koha.sekjal at gmail.com
          Severity: enhancement
    Classification: Unclassified
                OS: All
          Reporter: dpavlin at 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.