No subject

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.