Gah. I am a hacker of very little brain.

[denny]

I've been bitching about slow db responses here for days now. I've only just spotted the glaring error in my SQL that meant I was doing a join the size of an Ikea warehouse and returning the entire database about three times over. No wonder it was slow.

Working late tonight as penance.

Comments

[naranek.livejournal.com]

I once managed to kill our server with something like:

SELECT c1 FROM a, c2 FROM b, c3 FROM c WHERE a.fish = b.soup AND b.herring=c.wombat AND c.spong='42';

Where we had fast indices on b and c, but a was a big table with an index on `fish'. So: obvious thing to do is for MySQL to reorder the (straight) join so it grabbed c's index first, then worked back to b and a. But does it? Bollocks it does. It just does the join precisely as written, joyfully scanning through the whole of b and c for every row in a. Head, meet wall ... reordering the join 'SELECT c3 FROM c, c2 FROM b, c1 FROM a' fixes the problem nicely. Thank you, MySQL ...

My other amusing cock-up was indexing blobs: in order to avoid locking the table for potentially long periods, MySQL will create an on-disk temporary file. Ignore, for a moment, the fact that the only way to get case-sensitive searching to work is to use blobs. Ignore, for a moment, the sheer stupidity of assuming that variable-sized fields must be big. Concentrate instead on the fact that if it doesn't have an index, MySQL will happily copy entire columns into this temporary file to search them, blocking the table for far longer than the original query would've taken.

Eventually, we gave up and did the relevant join by hand (SELECT ref from answer where .., followed by SELECT data from answercontents where ansRef in ( ..) ). Arrrgh...