Just out of curiosity, how large is your database, Tasha? We've managed to run into issues w/ about 90K records (standard MARC bibs for books, etc.).
This isn't all that large in the grand scheme of things, so when we run into issues, I know either our SQL has some optimization issues, or the report may be a bit too ambitious (lots of UNION statements, etc.) & we should consider only running when the library's closed. We have a support vendor, so this is typically when we ask for help from people smarter than us :-) Wonder if you'd have better luck finding guidance on sites more focused on database administrators? (StackExchange, https://forums.mysql.com/, or even a local DBA/ MySQL user group) Cheers, Cab On Mon, Oct 11, 2021 at 9:52 PM Bales (US), Tasha R <[email protected]> wrote: > > Hi Cab, that is in fact helpful feedback. I've taken queries that we've used > for years with Millennium and Oracle, and am rewriting them for Koha and > MySQL. So far, MySQL does seem pickier. I have tried using the "EXPLAIN" > command to help identify inefficient queries, but it didn't really help. > > One tactic we've tried in the past is to create views containing subsets of > data that we need to frequently iterate over, to help decrease processing > time. Maybe that is an approach we could use here, but I am wary of trying > to modify our database in any way. Maybe a last resort. Thanks again. > > > Tasha Bales > Enterprise Services > http://isesi.web.boeing.com/ > > -----Original Message----- > From: Koha [mailto:[email protected]] On Behalf Of Cab Vinton > Sent: Thursday, October 7, 2021 03:49 > To: koha <[email protected]> > Subject: [EXTERNAL] Re: [Koha] Reports timing out, prevents access to web > interfaces > > EXT email: be mindful of links/attachments. > > > > I don't have any particularly helpful advice to offer, unfortunately, but I > can confirm from personal experience that certain queries can indeed bring > your system to a crawl. I don't think there's a solution to this unless > there's a way to firewall report queries from the rest of Koha, or to > automatically throttle misbehaving processes. > > In my case, I'd copied & pasted a report from the wiki, but a change in > Koha's database structure since the report's creation led to, well, very bad > results. > > I think your best bet is to take a really close look at the report to see if > there are ways to achieve the same results more efficiently. In our case, > this means seeking help from folks who are far more experienced with SQL than > we are. > > Best of luck! > > Cab Vinton, Director > Plaistow Public Library > Plaistow, NH > _______________________________________________ > > Koha mailing list http://koha-community.org [email protected] > Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha _______________________________________________ Koha mailing list http://koha-community.org [email protected] Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
