Rudi Ahlers wrote:
What else could cause this kind of problem?
You only have 449MB free on /tmp. It could easily fill that up during the query, and then delete the file before you run df again. Run it while the query is executing, I bet you see /tmp filling up.
Thanx, that seems to have solved the problem. I didn't think of checking to see if the tmp folder got full during the SQL statement execution. So, by increasing it to 1GB, the problem is solved
I've seen mysql do dumb stuff like building a huge temp table in a 3-way join before evaluating any of the WHEN clause that would eliminate most of it, but does it really have to make a copy of a single table to pick a random chunk?
This could very well be the case. The website in question is written in PERL (yea, I know.....) and the client doesn't really pay much for us to look into the code to see if it can be optimized either. So I just have to make sure the VPS copes with it. But for now it's working fine :)
There's nothing inherently wrong with perl for web programming and it doesn't have anything at all to do with the query passed to mysql. There are some quirks with apache/mod_perl that might matter for efficiency, though.