[Koha] Slow report for periodicals ranked by circ

Cab Vinton bibliwho at gmail.com
Fri Oct 6 05:41:52 NZDT 2017

Hi, All --

The following report takes several minutes to run, which leads to me
the sad probability that my SQL skillz are ... weak:

SELECT b.title, count(s.datetime) AS circs
FROM statistics s
  SELECT itemnumber, biblionumber, homebranch, itype FROM deleteditems
  SELECT itemnumber, biblionumber, homebranch, itype FROM items
) AS all_items USING (itemnumber)
LEFT JOIN biblio b ON (b.biblionumber=all_items.biblionumber)
WHERE all_items.itype = 'MAGAZINE' AND all_items.homebranch = <<Owning
     AND s.itemnumber IS NOT NULL AND s.type IN ('issue')
     AND s.datetime BETWEEN <<Between (yyyy-mm-dd)>> AND <<and (yyyy-mm-dd)>>
GROUP BY b.biblionumber

The report did run much quicker when I eliminated the UNION statement
& used just one of the Items tables.

Would be grateful to know if there's a better way of writing this report.

Many thanks,

Cab Vinton
Plaistow Public Library
Plaistow, NH

More information about the Koha mailing list