http://bugs.koha.org/cgi-bin/bugzilla/show_bug.cgi?id=2477 Summary: Improve Speed of the Shelf Browser Product: Koha Version: rel_3_2 Platform: PC OS/Version: All Status: NEW Severity: normal Priority: P3 Component: OPAC AssignedTo: jmf@liblime.com ReportedBy: jmf@liblime.com QAContact: koha-bugs@lists.koha.org
From Clay:
From Ryan: The concat is to make sure the shelf browser references the correct items. cn_sort is not unique, but should be unique for a title. Then the itemnumbers are unique within that title. By doing the concats, we can pick the unique items on either side of the physical shelf, and be sure that the specific copy of the book
Going into shelf browsing mode elicits two queries in the form of: SELECT * FROM items WHERE CONCAT(cn_sort,itemnumber) <= ? AND homebranch=? AND location=? ORDER BY CONCAT(cn_sort,itemnumber) DESC LIMIT 3; SELECT * FROM items WHERE CONCAT(cn_sort,itemnumber) >= ? AND homebranch=? AND location=? ORDER BY CONCAT(cn_sort,itemnumber) ASC LIMIT 3; Several problems with this. First, the CONCAT in the ordering statement is superfluous. There's no difference between the results of the above query and one sort with "ORDER BY cn_sort, itemnumber", yet the CONCAT() takes more time. Next, the cn_sort and location columns are not indexed. Third, the CONCAT() in the WHERE clause will kill any benefit gleaned from the indexing of cn_sort and itemnumber. I don't understand the koha schema very well, but is there any difference between the output from the above queries and ones like: SELECT * FROM items WHERE cn_sort <= ? AND itemnumber < ? AND homebranch=? AND location=? ORDER BY cn_sort,itemnumber DESC LIMIT 3; SELECT * FROM items WHERE cn_sort >= ? AND itemnumber > ? AND homebranch=? AND location=? ORDER BY cn_sort,itemnumber ASC LIMIT 3; This is just a further observation. I haven't empirically tested to see how much more efficient these changes would actually be. All I know for sure is that these queries comprise the bulk of the slow query log. that is showing a link to the shelf browser will be the one in the middle of the displayed set. So 1/ index location column 2/ consider implementing shelfbrowser as a separate table for the moment, since we shouldn't really be adding columns to the items table at this point.
From Galen:
Might something like this work? SELECT * FROM items WHERE ((cn_sort = ? AND itemnumber >= ?) OR (cn_sort > ?)) AND homebranch = ? AND location = ? ORDER BY cn_sort, itemnumber ASC LIMIT 3
From Joe:
I think we can change the ORDER BY clauses as recommended at the very least.
From Clay:
Unfortunately, because concat() evaluates to a string and ordering evaluates itemnumber as an integer, it doesn't work as expected because itemnumber rendered as string doesn't have a constant length. If there a way to zero pad an integer before squeezing it into a WHERE clause? ------- You are receiving this mail because: ------- You are the QA contact for the bug, or are watching the QA contact.