The following query is causing a mysql error The Error number is 28, which means low disk space. So I clear some space, retry and get the error again after a few times querying. After playing with query i find that the ORDER BY statement is what make it fail. If i remove it the script runs. So my question is, is there something about an ORDER BY statement that takes up a lot of disk space? I can sort in PHP, but if MySQL has the ability then i prefer it to sort with. Thanks for all the suggestions.
How big is the database? Also, have you adjusted sort_buffer_size? That controls how much memory is used for sorting. If there isn't enough, the server resorts to using swap which is really slow and may not be enough in your case.
The tmp file is filling up. After the query shuts down the file gets cleaned, so it looks like there is plenty of room. You need to resize this file. More information can be found here.
An order by statement requires that keys... unique,primary, index be placed on the columms you wish to sort by otherwise the sort relies on a disk write... Try adding this to the start of your queries... EXPLAIN...