I got itchy about magic quotes the other day because it’s the cause (through a fairly long cascade of errors) of some performance problems and runaways I’ve been seeing lately (pictured above).
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.