Slow query makes collection detailed list view hang

[avatar382]

I ran into this troubleshooting an Archon instance --

While accessing the detailed view of a collection, the app became completely unresponsive, with MySQL taking 100% of the CPU. A restart of the mysqld service was necessary to get the site functioning again.

The route that caused the database hang looked like this: archon-instance.com/index.php?p=collections/findingaid&id=249&q=video

I turned on the MySQL slow query log, and the following query showed up in the log, taking around 20 seconds per run. It seems to run forever:

SELECT ID,CollectionContentID FROM tblDigitalLibrary_DigitalContent WHERE CollectionContentID IN (SELECT CollectionContentID FROM tblDigitalLibrary_DigitalContent WHERE CollectionID = 249 AND CollectionContentID IN (1435) AND Browsable = 1 GROUP BY CollectionContentID HAVING COUNT(1) = 1);

Googling the query, I found this old thread on a discussion forum: http://forums.archon.org/viewtopic.php?f=5&t=960&start=15

Near the bottom of the thread, there was a patch suggested by user gordieschmitt for /packages/collections/lib/collection.inc.php:634, replacing:

$query = "SELECT ID,CollectionContentID FROM tblDigitalLibrary_DigitalContent WHERE CollectionContentID IN (SELECT CollectionContentID FROM tblDigitalLibrary_DigitalContent WHERE CollectionID = $this->ID AND CollectionContentID IN (" . implode(",", $contentKeys) . ") $browsable GROUP BY CollectionContentID HAVING COUNT(1) = 1)";

with

$query = "SELECT dd.ID,dd.CollectionContentID FROM tblDigitalLibrary_DigitalContent dd, (SELECT CollectionContentID FROM tblDigitalLibrary_DigitalContent WHERE CollectionID = $this->ID AND CollectionContentID IN (" . implode(",", $contentKeys) . ") $browsable GROUP BY CollectionContentID HAVING COUNT(1) = 1) a where dd.CollectionContentID = a.CollectionContentID";

I tried this patch, and it seems to resolve the issue in this case. This patch might be a good candidate for a future version.