[Koha-bugs] [Bug 2477] New: Improve Speed of the Shelf Browser

[bugzilla-daemon at pippin.metavore.com]

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 at liblime.com
        ReportedBy: jmf at liblime.com
         QAContact: koha-bugs at lists.koha.org


>From Clay:

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.

>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
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.