But I deserve most of the blame for allowing a query like this to run at all:
SELECT type, data, count(*) AS hits
WHERE data LIKE '%'
AND type IN ('subjkey','author', 'title')
GROUP BY data
ORDER BY hits DESC
As executed, it’s trying to select all 1.2 million records from the table, group them (a hugely memory intensive activity with text), and then report only the top seven.
It’s one of a few queries that returns the alternate searches in WPopac. I’m planning to rethink how that works, but I also should have built in a fail-safe to prevent it from trying to execute if the search variable is empty (the query above is written
WHERE data LIKE '$searchterms%' or something like that). I’m amused that MySQL tries so hard to fulfill the search — so hard that it’ll consume all available RAM and swap to do so.
The sysadmin and DBA are now much happier — along with any user frustrated by poor performance — with this patched.