But I deserve most of the blame for allowing a query like this to run at all:
SELECT type, data, count(*) AS hits FROM wpopac_WPopac_bibs_atsk <strong>WHERE data LIKE '%'</strong> AND type IN ('subjkey','author', 'title') GROUP BY data ORDER BY hits DESC LIMIT 7
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.