Bug 7886 - C4/ShelfBrowser slow SQL performance
Summary: C4/ShelfBrowser slow SQL performance
Status: CLOSED FIXED
Alias: None
Product: Koha
Classification: Unclassified
Component: Lists (show other bugs)
Version: 3.6
Hardware: All All
: P5 - low normal (vote)
Assignee: Dobrica Pavlinusic
QA Contact: Ian Walls
URL:
Keywords:
Depends on:
Blocks: 7119 7172
  Show dependency treegraph
 
Reported: 2012-04-03 12:52 UTC by Dobrica Pavlinusic
Modified: 2013-12-05 20:01 UTC (History)
1 user (show)

See Also:
Change sponsored?: ---
Patch complexity: ---
Documentation contact:
Documentation submission:
Text to go in the release notes:
Version(s) released in:


Attachments
Bug 7886 - C4/ShelfBrowser slow SQL performance (1.43 KB, patch)
2012-04-04 23:17 UTC, Dobrica Pavlinusic
Details | Diff | Splinter Review
Bug 7886 - C4/ShelfBrowser slow SQL performance (2.31 KB, patch)
2012-04-06 17:10 UTC, Dobrica Pavlinusic
Details | Diff | Splinter Review

Note You need to log in before you can comment on or make changes to this bug.
Description Dobrica Pavlinusic 2012-04-03 12:52:33 UTC
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.
Comment 1 Paul Poulain 2012-04-03 14:42:38 UTC
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 !
Comment 2 Paul Poulain 2012-04-03 14:43:10 UTC
I even think its more a bugfix than an ENH, so also upgrading severity !
Comment 3 Ian Walls 2012-04-03 15:05:01 UTC
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
Comment 4 Dobrica Pavlinusic 2012-04-04 23:17:42 UTC Comment hidden (obsolete)
Comment 5 Paul Poulain 2012-04-06 13:32:14 UTC
QA comment = dobrica, this patch is correct, but lack kohastructure.sql
Comment 6 Dobrica Pavlinusic 2012-04-06 14:29:21 UTC
Sorry, this is my first database patch, I will fix that.
Comment 7 Dobrica Pavlinusic 2012-04-06 17:10:14 UTC
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)
Comment 8 Ian Walls 2012-04-06 22:21:01 UTC
Signing off and Passing QA in one go, as this patch just adds indexes to essential fields, both for new and updating installs.
Comment 9 Jared Camins-Esakov 2012-05-24 00:56:26 UTC
This patch will appear in 3.6.5.